Link to home
Start Free TrialLog in
Avatar of ullenulle
ullenulleFlag for United States of America

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
Avatar of Sharath S
Sharath S
Flag of United States of America image

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.
Avatar of ullenulle

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
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.
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
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
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
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?
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
Avatar of Sharath S
Sharath S
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Thank you for your help.