sql server - Combine multiple rows into single row with separate columns in SQL -

i'm having following table structure , expected output listed below how achieve result

id -  date -   score  1  - 2013/04/13 - 100 2  - 2013/04/14 - 92  3  - 2013/04/15 - 33  

expected output :

date -2013-04-13 - 2013-04-14 -  2013-04-15  score -  100      -   92        -    33 

use pivot table operator:

select * (select date, score tablename) t pivot (   max(score)   date in([2013-04-13], [2013-04-14], [2013-04-15]) ) p; 

see in action here:

• sql fiddle demo

this give you:

| 2013-04-13 | 2013-04-14 | 2013-04-15 | ---------------------------------------- |        100 |         92 |         33 | 

---

if want dynamically, have use dynamic sql so. this:

declare @cols nvarchar(max); declare @query nvarchar(max);   select @cols = stuff((select distinct ',' +                         quotename(date)                       tablename                       xml path(''), type                      ).value('.', 'nvarchar(max)')                          , 1, 1, '');  set @query = 'select *               (select date, score tablename) t               pivot               (                 max(score)                 date in( ' + @cols+ ' )               ) p;';   execute(@query); 

see in action here:

• updated sql fiddle demo

note that: assuming have unique dates entries in table, in case max(score) work fine, in case there more dates entries same day can use sum instead. but, have use aggregate function pivot table operator.