ullenulle
asked on
Make query as fixed table
Hi there.
I have a query, that I may save as a view. That query/view contains a number of variables. One of them though can take either value A or B (arm A and B). So I want to evaluate all variables row by row distributed on the arms in columns like this:
Arm A Arm B
Var 1 eval 1a eval 1b
Var 2 eval 2a eval 2b
Var 3 eval 3a eval 3b
Var 4 eval 4a eval 4b
.... .... ....
and so on and on...
How do I formulate the query?
Thank you in advance. :-)
Best regards
Ulrich
I have a query, that I may save as a view. That query/view contains a number of variables. One of them though can take either value A or B (arm A and B). So I want to evaluate all variables row by row distributed on the arms in columns like this:
Arm A Arm B
Var 1 eval 1a eval 1b
Var 2 eval 2a eval 2b
Var 3 eval 3a eval 3b
Var 4 eval 4a eval 4b
.... .... ....
and so on and on...
How do I formulate the query?
Thank you in advance. :-)
Best regards
Ulrich
Didn't understand completely. How many variables you have?
What does your schema look like?
What does the data in the table look like?
The information you have provided is not enough for us to understand the question.
What does the data in the table look like?
The information you have provided is not enough for us to understand the question.
ASKER
Hi again.
Sorry for not providing enough info. I'll try harder. :-)
I can't show the specific variables, but consider I have a source table like this:
tbl_source
... with variables:
var_1
var_2
var_3
var_4
var_5
var_2 can only take the values A and B - as I call arm A and arm B.
var_1 is an ID.
The rest can be any value, and I want to make a table showing the distribution of the variables var_3, var_4 and var_5 like this:
Arm_A Arm_B
var_3 if=1 eval_3a_1 eval_3b_1
var_3 if=2 eval_3a_2 eval_3b_2
var_3 if=3 eval_3a_3 eval_3b_3
var_4 if=right eval_4a_right eval_4b_right
var_4 if=left eval_4a_left eval_4b_left
var_5 if=yes eval_5a_yes eval_5b_yes
var_5 if=no eval_5a_no eval_5b_no
var_5 if=maybe eval_5a_maybe eval_5b_maybe
var_5 if=n/a eval_5a_n/a eval_5b_n/a
So I need to make a query, that look in a way like this (not correct syntax at all (!!!!), but to explain what I want. :-)
SELECT
CASE WHEN var_2='A' THEN SUM(CASE WHEN var_3=1 THEN var_3 ELSE 0 END) END AS Arm_A,
CASE WHEN var_2='B' THEN SUM(CASE WHEN var_3=1 THEN var_3 ELSE 0 END) END AS Arm_B,
CASE WHEN var_2='A' THEN SUM(CASE WHEN var_3=2 THEN var_3 ELSE 0 END) END AS Arm_A,
CASE WHEN var_2='B' THEN SUM(CASE WHEN var_3=2 THEN var_3 ELSE 0 END) END AS Arm_B,
... and so and on...
FROM tbl_source
I know very well, that the above won't work! The first 2 lines could work, but I kinda need to make some "GROUP BY" that will group on the optional values of var_3 to var_5.
I'm afraid this is the best I can explain it. The real world won't make it any better! Believe me. :-)
I hope you got an idea of my question now?
Best regards
Ulrich
Sorry for not providing enough info. I'll try harder. :-)
I can't show the specific variables, but consider I have a source table like this:
tbl_source
... with variables:
var_1
var_2
var_3
var_4
var_5
var_2 can only take the values A and B - as I call arm A and arm B.
var_1 is an ID.
The rest can be any value, and I want to make a table showing the distribution of the variables var_3, var_4 and var_5 like this:
Arm_A Arm_B
var_3 if=1 eval_3a_1 eval_3b_1
var_3 if=2 eval_3a_2 eval_3b_2
var_3 if=3 eval_3a_3 eval_3b_3
var_4 if=right eval_4a_right eval_4b_right
var_4 if=left eval_4a_left eval_4b_left
var_5 if=yes eval_5a_yes eval_5b_yes
var_5 if=no eval_5a_no eval_5b_no
var_5 if=maybe eval_5a_maybe eval_5b_maybe
var_5 if=n/a eval_5a_n/a eval_5b_n/a
So I need to make a query, that look in a way like this (not correct syntax at all (!!!!), but to explain what I want. :-)
SELECT
CASE WHEN var_2='A' THEN SUM(CASE WHEN var_3=1 THEN var_3 ELSE 0 END) END AS Arm_A,
CASE WHEN var_2='B' THEN SUM(CASE WHEN var_3=1 THEN var_3 ELSE 0 END) END AS Arm_B,
CASE WHEN var_2='A' THEN SUM(CASE WHEN var_3=2 THEN var_3 ELSE 0 END) END AS Arm_A,
CASE WHEN var_2='B' THEN SUM(CASE WHEN var_3=2 THEN var_3 ELSE 0 END) END AS Arm_B,
... and so and on...
FROM tbl_source
I know very well, that the above won't work! The first 2 lines could work, but I kinda need to make some "GROUP BY" that will group on the optional values of var_3 to var_5.
I'm afraid this is the best I can explain it. The real world won't make it any better! Believe me. :-)
I hope you got an idea of my question now?
Best regards
Ulrich
Can you explain with some sample data from your table and expected result?
The creation of second table may be avoided if the requirement is clear. Looks like you need a dynamic query, but please provide some sample data.
The creation of second table may be avoided if the requirement is clear. Looks like you need a dynamic query, but please provide some sample data.
ASKER
Sure. Here's how the source could look like:
Record no. var_1 var_2 var_3 var_4 var_5
1 1 A 1 right no
2 2 B 3 left maybe
3 3 B 2 left n/a
4 4 A 2 right yes
5 5 A 1 right maybe
6 6 B 2 left yes
7 7 A 1 right no
8 8 A 2 right yes
9 9 B 1 left no
10 10 A 3 right no
The result, I want to obtain with a query, would look like this with the sample above:
Arm_A Arm_B
var_3 if=1 3 1
var_3 if=2 2 2
var_3 if=3 1 1
var_4 if=right 6 0
var_4 if=left 0 4
var_5 if=yes 2 1
var_5 if=no 3 1
var_5 if=maybe 1 1
var_5 if=n/a 0 1
Record no. var_1 var_2 var_3 var_4 var_5
1 1 A 1 right no
2 2 B 3 left maybe
3 3 B 2 left n/a
4 4 A 2 right yes
5 5 A 1 right maybe
6 6 B 2 left yes
7 7 A 1 right no
8 8 A 2 right yes
9 9 B 1 left no
10 10 A 3 right no
The result, I want to obtain with a query, would look like this with the sample above:
Arm_A Arm_B
var_3 if=1 3 1
var_3 if=2 2 2
var_3 if=3 1 1
var_4 if=right 6 0
var_4 if=left 0 4
var_5 if=yes 2 1
var_5 if=no 3 1
var_5 if=maybe 1 1
var_5 if=n/a 0 1
How did you derive 3 for var_3 if=1 and Arm_A?
For var_3 if=1, there are 5 records with Var_2 as A. Isn't is sum of all var_2 values for highlighted records.
Record no. var_1 var_2 var_3 var_4 var_5
1 1 A 1 right no
2 2 B 3 left maybe
3 3 B 2 left n/a
4 4 A 2 right yes
5 5 A 1 right maybe
6 6 B 2 left yes
7 7 A 1 right no
8 8 A 2 right yes
9 9 B 1 left no
10 10 A 3 right no
For var_3 if=1, there are 5 records with Var_2 as A. Isn't is sum of all var_2 values for highlighted records.
Record no. var_1 var_2 var_3 var_4 var_5
1 1 A 1 right no
2 2 B 3 left maybe
3 3 B 2 left n/a
4 4 A 2 right yes
5 5 A 1 right maybe
6 6 B 2 left yes
7 7 A 1 right no
8 8 A 2 right yes
9 9 B 1 left no
10 10 A 3 right no
ASKER
In case var_2=A, then var_3 hold the value "1" 3 times as marked bold:
Record no. var_1 var_2 var_3 var_4 var_5
1 1 A 1 right no
2 2 B 3 left maybe
3 3 B 2 left n/a
4 4 A 2 right yes
5 5 A 1 right maybe
6 6 B 2 left yes
7 7 A 1 right no
8 8 A 2 right yes
9 9 B 1 left no
10 10 A 3 right no
Record no. var_1 var_2 var_3 var_4 var_5
1 1 A 1 right no
2 2 B 3 left maybe
3 3 B 2 left n/a
4 4 A 2 right yes
5 5 A 1 right maybe
6 6 B 2 left yes
7 7 A 1 right no
8 8 A 2 right yes
9 9 B 1 left no
10 10 A 3 right no
Ok. how many variables you have? Do you know the distinct values in those variables like var_3 has 1,2,3 values and var_4 = right, left etc.
Or do you want to derive them dynamically?
Or do you want to derive them dynamically?
ASKER
There's like 35 variables, and they can all be categorized like left/right, specific values etc... There's no unknown values. Data has been entered via drop down menus or check boxes. That way there's limited options for each variable.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi there.
Forgive me for not responding. I've been caught up other urgent assignments. I'll be back in a few days.
Best regards
Ulrich
Forgive me for not responding. I've been caught up other urgent assignments. I'll be back in a few days.
Best regards
Ulrich
ASKER
Thank you for your help.