I'm having the following table structure and the expected output is listed below how to achieve the 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 the PIVOT table operator:
SELECT *
FROM (SELECT date, score FROM tablename) AS t
PIVOT
(
MAX(Score)
FOR date IN([2013-04-13], [2013-04-14], [2013-04-15])
) AS p;
See it in action here:
This will give you:
| 2013-04-13 | 2013-04-14 | 2013-04-15 |
----------------------------------------
| 100 | 92 | 33 |
If you want to do this dynamically, you have to use dynamic sql to do so. Something like this:
DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);
select @cols = STUFF((SELECT distinct ',' +
QUOTENAME(date)
FROM tablename
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
, 1, 1, '');
SET @query = 'SELECT *
FROM (SELECT date, score FROM tablename) AS t
PIVOT
(
MAX(Score)
FOR date IN( ' + @cols+ ' )
) AS p;';
EXECUTE(@query);
See it in action here:
Note that: I am assuming that you have unique dates entries in your table, in this case the MAX(score) will work fine, in case there is more dates entries for the same day you can use SUM instead. But, you have to use any aggregate function with the PIVOT table operator.
