Row to Column in Sql Server 2008

Question

 Table - T1 (id, Description)
 Table - T2 (id, fk_id,Dates, Amount)

select d.Description,dt.Dates,dt.Amount
 from T2 as dt join T1 d
  on
 d.id = dt.fk_id
group by d.Description,dt.Dates,dt.Amount

running above query results shows below

Description       Dates           Amount
nutrition1      2012-01-01      100.00
nutrition1      2013-01-01      100.00
nutrition2      2012-01-01      100.00
nutrition2      2013-01-01      600.00
nutrition3      2012-01-01      300.00
nutrition3      2013-01-01      700.00
nutrition4      2012-01-01      400.00
nutrition4      2013-01-01      800.00
nutrition5      2012-01-01      300.00
nutrition5      2013-01-01      600.00

i want to show result as

Description    01-01-2012  01-01-2013
nutrition1       100          100
nutrition2       100          600
nutrition3       300          700
nutrition4       400          800
nutrition5       300          600

Dates will change

You can do by using `XML path` – Satinder singh Jan 04 '13 at 11:47 — Satinder singh, Jan 04 '13 at 11:47

Taryn · Accepted Answer · 2013-01-04T12:06:55.797

This type of transformation is a PIVOT. If you know the values ahead of time you can hard-code them similar to this:

select *
from
(
  select d.Description,convert(char(10), dt.dates, 110) dates,dt.Amount
  from T2 as dt 
  join T1 d
    on d.id = dt.fk_id
) src
pivot
(
  sum(amount)
  for dates in ([01-01-2012], [01-01-2013])
) piv

See SQL Fiddle with Demo

If you have unknown values to transform, then you will want to use dynamic sql:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(convert(char(10), dates, 110)) 
                    from T2
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT Description, ' + @cols + ' from 
             (
                select d.Description,convert(char(10), dt.dates, 110) dates,dt.Amount
                from T2 as dt 
                join T1 d
                  on d.id = dt.fk_id
            ) x
            pivot 
            (
                sum(amount)
                for dates in (' + @cols + ')
            ) p '

execute(@query)

See SQL Fiddle with Demo

Both versions produce the same result:

| DESCRIPTION | 01-01-2012 | 01-01-2013 |
-----------------------------------------
|  nutrition1 |        100 |        100 |
|  nutrition2 |        100 |        600 |
|  nutrition3 |        300 |        700 |
|  nutrition4 |        400 |        800 |
|  nutrition5 |        300 |        600 |

score 0 · Answer 2 · answered Jan 04 '13 at 11:53

0

SELECT Description,
  SUM(
  CASE
    WHEN dt.Dates=to_date('01-01-2012','dd-mm-yyyy')
    THEN dt.Amount
    ELSE 0
  END) AS '01-01-2012' ,
  SUM(
  CASE
    WHEN dt.Dates=to_date('01-01-2013','dd-mm-yyyy')
    THEN dt.Amount
    ELSE 0
  END) '01-01-2013'
FROM T2 AS dt
JOIN T1 d
ON d.id = dt.fk_id
GROUP BY d.Description;

answered Jan 04 '13 at 11:53

Mariappan Subramanian

9,527
8
32
33

1

Thanks for your reply but dates will change. – Subin Jan 04 '13 at 12:00

Row to Column in Sql Server 2008

2 Answers2