Convert row to columns SQL dynamically

Question

I have a table Dev with the data below

YYYMMDD   Atest  BTest   CTest

20150525   100    200    300
20150526   110    210    310
20150527   120    220    320

I need output like below

     xyz  20150525  201050526  20150527

    Atest 100       110         120
    BTest 200        210        220
    CTest 300        310        320

How can i achieve above result set. My table Dev will grow and i need the result set table to build columns dynamically and display the data as required. Any help is appreciated. If you suggest pivot, may i know what field should i use for aggregation and how to use it. Thanks.

What field should i use for aggregation here like min(choice) in http://stackoverflow.com/questions/11985796/pivot-dynamic-columns-no-aggregation — user28455, Jun 15 '15 at 19:40

score 0 · Accepted Answer · edited May 23 '17 at 12:05

0

First you will need to unpivot your table and after that pivot again. This is the key idea:

select * from TableName
unpivot(v for xyz in([Atest],[Btest],[Ctest]))u
pivot(max(v) for yyyymmdd in([20150525],[20150526],[20150527]))p

Fiddle http://sqlfiddle.com/#!3/a675c/1

As for dynamic sql, you can find many example of how you can use STUFF function to concatenate distinct dates in one string and construct dynamic query. For instance T-SQL dynamic pivot

edited May 23 '17 at 12:05

Community

1
1

answered Jun 15 '15 at 19:59

Giorgi Nakeuri

35,155
8
47
75

Thanks Nakeuri. That worked. I will try to use STUFF function now. – user28455 Jun 15 '15 at 20:48

Convert row to columns SQL dynamically

1 Answers1