My table is like this:
Sitecode Month Amount -------- ----- ------ XX Jan 1000 XX Jan 3000 XX Apr 3000 XX Apr 1000
What I want is, to show the result something like this:
Sitecode MonthJAN MonthAPR -------- -------- -------- XX 4000 4000
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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
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.
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.
select * from ( select sitecode, [month], amount from yourtable ) src pivot ( sum(amount) for month in (Jan, Apr) ) piv;
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.
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)
All 3 versions of this will return the same result:
| SITECODE | JAN | APR | -------------------------- | XX | 4000 | 4000 |
As Martin Smith said, you need to pivot the data, whether with an explicit PIVOT as referenced or something like this (SQL Fiddle):
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 Result GROUP BY SiteCode;