CODEWITHSUNDEEP
sqlsql-servert-sqlpivot
JBone
JBone

Reputation: 3203

Pivot data in T-SQL

I have a group of people. Lets call them A,B,C. I have a table that shows how much they were paid each month....

PERSON|MONTH|PAID
A      JAN   10
A      FEB   20   
B      JAN   10   
B      FEB   20   
B      SEP   30   
C      JAN   10   
C      JUNE  20   
C      JULY  30   
C      SEP   40

THIS table can and does go on for years and years..

Is there a way to pivot this table (nothing as I see really needs to be aggregated which is usually done in pivots) In a table that looks like the following?

     JAN    FEB    MAR    APR    MAY    JUN    JUL    AGU    SEP
A    10     20
B    10     20     -      -      -      -      -      -      30
C    10     -      -      -      -      20     30     -      40

Haven't run into something like this before but assume it is a common problem any ideas?

Upvotes: 5

Views: 1731

Answers (2)

Kenneth
Kenneth

Reputation: 1364

I am not sure why you need a dynamic # of columns, since there are always 12 months in a year. Also your month names seem a bit inconsistent in length.

Sample result set:

SELECT * FROM (SELECT 'A' [PERSON],'JAN' [MONTH],'10' [PAID]
UNION SELECT 'A','FEB',20
UNION SELECT 'B','JAN',10
UNION SELECT 'B','FEB',20
UNION SELECT 'B','SEP',30
UNION SELECT 'C','JAN',10
UNION SELECT 'C','JUNE',20
UNION SELECT 'C','JULY',30
UNION SELECT 'C','SEP',40) AS A
PIVOT (SUM([PAID]) FOR [MONTH] IN ([JAN],[FEB],[MARCH],[APRIL],[MAY],[JUNE],[JULY],[AUG],[SEP],[OCT],[NOV],[DEC])) p

Against your table this would become:

SELECT [PERSON],[MONTH],[PAID]
FROM [YOURTABLE]
PIVOT (SUM([PAID]) FOR [MONTH] IN ([JAN],[FEB],[MARCH],[APRIL],[MAY],[JUNE],[JULY],[AUG],[SEP],[OCT],[NOV],[DEC])) p

If you add a year column it looks like this:

SELECT * FROM (SELECT 'A' [PERSON],'JAN' [MONTH],'10' [PAID], 2011 [YEAR]
UNION SELECT 'A','FEB',20, 2011
UNION SELECT 'B','JAN',10, 2011
UNION SELECT 'A','FEB',20, 2010
UNION SELECT 'B','JAN',10, 2010
UNION SELECT 'B','FEB',20,2011
UNION SELECT 'B','SEP',30,2011
UNION SELECT 'C','JAN',10,2011
UNION SELECT 'C','JUNE',20,2011
UNION SELECT 'C','JULY',30,2011
UNION SELECT 'C','SEP',40,2011) AS A
PIVOT (SUM([PAID]) FOR [MONTH] IN ([JAN],[FEB],[MARCH],[APRIL],[MAY],[JUNE],[JULY],[AUG],[SEP],[OCT],[NOV],[DEC])) p

Upvotes: 2

Adriano Carneiro
Adriano Carneiro

Reputation: 58595

If you are using SQL Server 2005 (or above), here is the code:

DECLARE @cols VARCHAR(1000)
DECLARE @sqlquery VARCHAR(2000)

SELECT  @cols = STUFF(( SELECT distinct  ',' + QuoteName([Month])
                        FROM YourTable FOR XML PATH('') ), 1, 1, '') 


SET @sqlquery = 'SELECT * FROM
      (SELECT Person, Month, Paid
       FROM YourTable ) base
       PIVOT (Sum(Paid) FOR [Person]
       IN (' + @cols + ')) AS finalpivot'

EXECUTE ( @sqlquery )

This will work no matter how many different status you have. It dynamically assembles a query with PIVOT. The only way you can do PIVOT with dynamic columns is by assembling the the query dynamically, which can be done in SQL Server.

Other examples:

Upvotes: 3

Related Questions