Grouping Data by Column

Question

Possible Duplicate:
dynamic sql pivot in sql server

I have a table called Col_values and Col_ID is an Identity field the data looks like:

Val_ID       Col_ID       Value
76951792     3            Closed
76951791     3            Closed
76951790     25           Open
76951789     25           Closed
76951792     1            US
76951791     1            Canada

and another table called Et_Col and the data looks like:

Col_ID   Col_Name   D_ID
3        Status     1
25       Status     2
1        Country    1

I want the output as:

Val_ID       Status    Country
76951792    Closed     US
76951791    Closed     Canada
76951790    Open       Null
76951789    Closed     Null

So I wrote a cursor to get the output and the cursor returns the statement like:

  Select val_ID
    ,max(case when Col_ID = 3 then Value end) as Status
    ,max(case when Col_ID = 25 then Value end) as Status
   ,max(CASE WHEN Col_ID = 1 THEN VALUE END ) AS Country
    From Col_values
    Group by Val_ID

this gives me output as:

Val_ID    Status    Status    Country
76951792  Closed    Null      US
76951791  Closed    Null      Canada
76951789  Null      Closed    Null
76951790  Null      Open      Null

How can I fix This?

This can be done with a dynamic pivot table: http://stackoverflow.com/questions/8327261/dynamic-sql-pivot-in-sql-server — JeffO, Dec 19 '11 at 18:07

score 1 · Answer 1 · answered Dec 19 '11 at 17:38

1

select val_id, value as Status
from col_values

That's what would yield your desired output. Why do you think you need to group data to get that??

answered Dec 19 '11 at 17:38

Please see my edited question. I have put some more sample data in the tables. So that you can see why I am grouping the data – peter Dec 19 '11 at 17:41
Pivot is the key word your looking for: Very similar to http://stackoverflow.com/questions/1677645/using-pivot-in-sql-server-2008 – xQbert Dec 19 '11 at 17:50

Jon Egerton · Accepted Answer · 2011-12-19T18:11:00.070

1

It should be possible to change your cursor so that it generates sql like this then:

select 
    val_id
    ,max(case when Col_name = 'Status' then Value end) as Status
    ,max(case when Col_name = 'Country' then Value end) as Country
from
    col_values v
    join ET_Col c on v.col_id=c.col_id

group by val_id

The different is that you are using the col_name to split between columns instead of the col_id, so the doubled up column names will merge.

edited Dec 19 '11 at 18:11

answered Dec 19 '11 at 17:51

Jon Egerton

40,401
11
97
129

I just gave you some sample data but there are around 100 Col_ID's. So I cannot write SQl that's why i wrote cursor which gives me the select statement i wrote above – peter Dec 19 '11 at 17:54
I've updated. I think you'll be able to generate the sql above fairly easily based on what you're already able to generate. – Jon Egerton Dec 19 '11 at 18:11

Grouping Data by Column

2 Answers2