Static and Dynamic value to Column Name in SQL table

Question

Check the following table:

--------------------------------
ID    Value    GroupName
--------------------------------
1     10       A
2     12       B
3     19       A
4     5        B
5     9        A
6     12       B
7     17       C
8     21       C
9     8        C
10    15       A
--------------------------------

I want to have the following output:

-------------------------
A     B      C
-------------------------
10    12     17
19    5      21
9     12     8
15    NULL   NULL
-------------------------

So, all values in 'GroupName' field will become a new column.
Does anyone know how to do this in SQL 2008?

Thank you.

`SELECT (CASE WHEN GroupName='A' THEN Value END) A,(CASE WHEN GroupName='B' THEN Value END) B,(CASE WHEN GroupName='C' THEN Value END) C FROM table` — Mihai, Aug 07 '14 at 06:31
@Mihai, How if I don't know what is the next value in 'GroupName' field? — Haminteu, Aug 07 '14 at 06:36
I think you can do it with dynamic sql but this is beyond my knowledge look here http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query — Mihai, Aug 07 '14 at 06:39

TechDo · Accepted Answer · 2014-08-07T06:52:04.900

2

Please try using PIVOT; and the given below example is for only 3 static values A, B and C:

select [A], [B], [C] From(
    select 
        Value, GroupName, 
        ROW_NUMBER() over (partition by GroupName order by GroupName) RN
    From 
        tbl
)up pivot (sum(value) for GroupName in ([A], [B], [C]))x

For dynamic values,

DECLARE @pivv NVARCHAR(MAX),@Query NVARCHAR(MAX)

SELECT @pivv=COALESCE(@pivv+',','')+ QUOTENAME(GroupName) 
from tbl group by GroupName

IF ISNULL(@pivv, '')<>''
set @Query=
    'select '+@pivv+' From(
        select 
            Value, GroupName, 
            ROW_NUMBER() over (partition by GroupName order by GroupName) RN
        From 
            tbl
    )up pivot (sum(value) for GroupName in ('+@pivv+'))x'

exec (@Query)

SQL Fiddle Demo

edited Aug 07 '14 at 06:52

answered Aug 07 '14 at 06:38

TechDo

18,398
3
51
64

If you don't mind. Would you please complete the answer? Dynamic values? Thank you. – Haminteu Aug 07 '14 at 06:48
For dynamic values, It has to be done in Storeprocedure, right? Or possible just run in view? – Haminteu Aug 07 '14 at 07:16
Yes, you can go for a stored procedure. View wont work I guess. – TechDo Aug 07 '14 at 07:18
Okay. One more thing, Is it possible to make an alias for each column? ColumnName as myColumn, something like that. – Haminteu Aug 07 '14 at 07:31

Static and Dynamic value to Column Name in SQL table

1 Answers1