As others have said this is known as a PIVOT
. There are several ways in which you can transform your data from rows into columns of data.
If you know the values ahead of time, then you can hard-code the values. Prior to the PIVOT
function you would use an aggregate function with a CASE
statement.
Aggregate/CASE Version:
select sitecode,
sum(case when [month] = 'Jan' then amount else 0 end) MonthJan,
sum(case when [month] = 'Apr' then amount else 0 end) MonthApr
from yourtable
group by sitecode;
See SQL Fiddle with Demo.
The PIVOT
function was made available in SQL Server 2005, so if you are using that version or a newer one then you can apply that to your data.
Static PIVOT:
select *
from
(
select sitecode,
[month],
amount
from yourtable
) src
pivot
(
sum(amount)
for month in (Jan, Apr)
) piv;
See SQL Fiddle with Demo
The above two versions work great if you know the values ahead of time. If not, then you will use dynamic sql to create the result.
Dynamic PIVOT:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(month)
from yourtable
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT sitecode,' + @cols + ' from
(
select sitecode, [month], amount
from yourtable
) x
pivot
(
sum(amount)
for month in (' + @cols + ')
) p '
execute(@query)
See SQL Fiddle with Demo
All 3 versions of this will return the same result:
| SITECODE | JAN | APR |
--------------------------
| XX | 4000 | 4000 |