Transpose row to column in SQL Server 2008

Question

I have the following table:

oGroup     oDate          oValue
--------------------------------
A          2014-01-01     20
A          2014-01-02     30
B          2014-01-01     5
B          2014-01-02     15
C          2014-01-01     40
C          2014-01-02     60

I want to have the following result:

oGroup   2014-01-01     2014-01-02
----------------------------------
A        20             30
B        5              15
C        40             60

How can I achieve this in SQL Server 2008?

Thank you.

You can achieve this through `PIVOT` or using `CROSS TAB`. Search for `PIVOT` in google you will get thousands of answer — Pரதீப், Mar 24 '15 at 04:27
For reference check this answer http://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server and http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/ — Pரதீப், Mar 24 '15 at 04:30

vhadalgi · Accepted Answer · 2015-03-24T05:01:25.237

Learn on Pivot

select * from piv
pivot
(
min(oValue)
for oDate in([2014-01-01],[2014-01-02])
)as pivv;

Using Dynamic sql

declare @query nvarchar(max)
declare @cols nvarchar(max)

select @cols=stuff((select distinct ','+QUOTENAME(oDate) from piv for xml path(''),TYPE).value('.','nvarchar(max)'),1,1,'')

select @query='select * from piv
pivot
(
min(oValue)
for oDate in(' + @cols + ')
)as pivv;'

exec (@query)

Fiddle Demo

Felix Pamittan · Answer 2 · 2015-03-24T04:53:07.227

You could use dynamic crosstab:

DECLARE 
    @sql1   VARCHAR(4000) = '',
    @sql2   VARCHAR(4000) = '',
    @sql3   VARCHAR(4000) = ''

SELECT @sql1 = 
'SELECT
    oGroup' + CHAR(10)

SELECT @sql2 = @sql2 +
'   ,MAX(CASE WHEN oDate = CAST(''' + CONVERT(VARCHAR(10), oDate, 112) + ''' AS DATE) THEN oValue END) AS [' + CONVERT(VARCHAR(10), oDate, 120) +']' + CHAR(10)
FROM(
    SELECT DISTINCT oDate FROM SampleData
)t
ORDER BY oDate

SELECT @sql3 = 
'FROM SampleData
GROUP BY oGroup
ORDER BY oGroup'

PRINT(@sql1 + @sql2 +@sql3)
EXEC (@sql1 + @sql2 +@sql3)

SQL Fiddle

This is what the PRINT outputs:

SELECT
    oGroup
    ,MAX(CASE WHEN oDate = CAST('20140101' AS DATE) THEN oValue END) AS [2014-01-01]
    ,MAX(CASE WHEN oDate = CAST('20140102' AS DATE) THEN oValue END) AS [2014-01-02]
FROM SampleData
GROUP BY oGroup
ORDER BY oGroup

score 0 · Answer 3 · edited Mar 24 '15 at 04:52

0

Try this

I am not sure ,I havn't try this

SELECT a.ogroup,
       a.ovalue AS '2014-01-01',
       b.ovalue AS '2014-01-02'
FROM   (SELECT *
        FROM   table
        WHERE  odate = '2014-01-01') AS a
       JOIN (SELECT *
             FROM   table
             WHERE  odate = '2014-01-02') AS b
         ON b.ogroup = a.ogroup

edited Mar 24 '15 at 04:52

Ajay2707

5,690
6
40
58

answered Mar 24 '15 at 04:38

SimarjeetSingh Panghlia

2,200
13
14

Transpose row to column in SQL Server 2008

3 Answers3