Take the 2-minute tour ×
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's 100% free, no registration required.

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
share|improve this question

closed as not a real question by Phil, ypercube, Martin Smith, Mark Storey-Smith, dezso Jan 16 '13 at 11:06

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

Great! Now what? –  Coder anonymous Jan 11 '13 at 12:12
Please solution –  user16947 Jan 11 '13 at 13:19
You need a PIVOT query. Plenty of examples on Stackoverflow –  Martin Smith Jan 11 '13 at 13:29
The reason you have so many down votes is the question is asked very often and you can probably find many many examples of how to do this on this site. –  Zane Jan 11 '13 at 14:40

2 Answers 2

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 *
  select sitecode,
  from yourtable
) src
  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:

    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(month) 
                    from yourtable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 

set @query = 'SELECT sitecode,' + @cols + ' from 
                select sitecode, [month], amount
                from yourtable
            ) x
                for month in (' + @cols + ')
            ) p '


See SQL Fiddle with Demo

All 3 versions of this will return the same result:

|       XX | 4000 | 4000 |
share|improve this answer
+1 for a very thorough answer. –  Leigh Riffel Jan 11 '13 at 15:12
@bluefeet nice answer.. i was searching the same in mysql... so how will the dynamic pivot query is written in mysql? ..and this is my fiddle sqlfiddle.com/#!2/6ddf14/31 where i need to find the sum of ordered_qty by color over year –  x-code Jan 8 at 0:02
@x-code The syntax for MySQL is completely different - look at my answer here - dba.stackexchange.com/questions/45995/… –  bluefeet Jan 8 at 14:57

As Martin Smith said, you need to pivot the data, whether with an explicit PIVOT as referenced or something like this (SQL Fiddle):

   , 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;
share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.