big sql problem

[michelleqw]

Dear sql users,

At the moment we do have a problem and we can't find a solution:

Tables:

GROUP_ CHOICE VALUE
x A 1
x B 2
x C 3
x A 4
x B 5
x C 6

convert to:

GROUP_ CH_A_VALUE CH_B_VALUE CH_C_VALUE
X 5 7 9

We want to make a counting of the field CHOICE and put the value´s in new fields. All must be happen in one sql statement.

Can someone give us a solution for this problem or give a sql statement?

Nice regards,

Michelle.

 

[PHV]

In your DBMS documentation search for Crosstab query or Pivot table.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[JarlH]

SELECT GROUP_,
SUM(CASE CHOICE WHEN 'A' THEN value
ELSE 0 END) as CH_A_VALUE,
SUM(CASE CHOICE WHEN 'B' THEN value
ELSE 0 END) as CH_B_VALUE,
SUM(CASE CHOICE WHEN 'C' THEN value
ELSE 0 END) as CH_C_VALUE
FROM sometable
GROUP BY GROUP_


Note that VALUE and GROUP are bad column names, these words are SQL keywords and GROUP is even reserved. (As you have already notived...)

 

[michelleqw]

thanks for the information,

Michelle.