CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Haminteu
Haminteu

Reputation: 1334

Transpose row to column in SQL Server 2008

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.

Upvotes: 0

Views: 124

Answers (3)

vhadalgi
vhadalgi

Reputation: 7189

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

Upvotes: 4

Felix Pamittan
Felix Pamittan

Reputation: 31879

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

Upvotes: 2

SimarjeetSingh Panghlia
SimarjeetSingh Panghlia

Reputation: 2200

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

Upvotes: 0

Related Questions