Turning Rows into Columns

[markjerz@googlemail.com]

Say I have a table of data containing something like

Region | County | Year | Month | Value

for some sort of value (int). I want to re-arrange this data so that it
comes out like this:

Region | County | Year | J | F | M | A | M | J | J | A | S | O | N | D

where the letters are obviously the months in order. How would I go
about this/what's the best way. I attempted to use 12 INNER JOINS on
the table itself, sadly that failed miserably. Also, this doesn't seem
very efficient?

Before you ask I got rid of my original code (gave up!)

[Plamen Ratchev]

Re: Turning Rows into Columns

What you need is table pivoting (normally done best in reporting tools). If
you have to do it in SQL, here are a couple options:

SQL Server 2005 only (using the PIVOT table operator, you can look it up in
SQL Server 2005 BOL):

SELECT M1.Region, M1.County, M1.Year,
SUM([Jan]) AS [Jan],
SUM([Feb]) AS [Feb],
SUM([Mar]) AS [Mar],
SUM([Apr]) AS [Apr],
SUM([May]) AS [May],
SUM([Jun]) AS [Jun],
SUM([Jul]) AS [Jul],
SUM([Aug]) AS [Aug],
SUM([Sep]) AS [Sep],
SUM([Oct]) AS [Oct],
SUM([Nov]) AS [Nov],
SUM([Dec]) AS [Dec]
FROM MyTable AS M1
INNER JOIN (SELECT Region, County, Year, Value, Month
FROM MyTable) AS M2
PIVOT ( SUM(Value) FOR Month IN ([Jan],
[Feb], [Mar], [Apr], [May], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov],
[Dec])) AS M3
ON M1.Region = M3.Region AND M1.County = M3.County AND
M1.Year = M3.Year
GROUP BY M1.Region, M1.County, M1.Year


SQL Server 2000 and SQL Server 2005:

SELECT Region, County, Year,
SUM(case when Month = 'Jan' then Value else 0 end) AS [Jan],
SUM(case when Month = 'Feb' then Value else 0 end) AS [Feb],
SUM(case when Month = 'Mar' then Value else 0 end) AS [Mar],
SUM(case when Month = 'Apr' then Value else 0 end) AS [Apr],
SUM(case when Month = 'May' then Value else 0 end) AS [May],
SUM(case when Month = 'Jun' then Value else 0 end) AS [Jun],
SUM(case when Month = 'Jul' then Value else 0 end) AS [Jul],
SUM(case when Month = 'Aug' then Value else 0 end) AS [Aug],
SUM(case when Month = 'Sep' then Value else 0 end) AS [Sep],
SUM(case when Month = 'Oct' then Value else 0 end) AS [Oct],
SUM(case when Month = 'Nov' then Value else 0 end) AS [Nov],
SUM(case when Month = 'Dec' then Value else 0 end) AS [Dec]
FROM MyTable
GROUP BY Region, County, Year


In both cases you have to replace the Jan, Feb, etc. with the correct
spelling of the months in your database.

Regards,

Plamen Ratchev

http://www.SQLStudio.com

[Plamen Ratchev]

Re: Turning Rows into Columns

Oops, no need to SUM when using PIVOT, it should be like this:

SELECT DISTINCT M1.Region, M1.County, M1.Year,
[Jan],
[Feb],
[Mar],
[Apr],
[May],
[Jun],
[Jul],
[Aug],
[Sep],
[Oct],
[Nov],
[Dec]
FROM MyTable AS M1
INNER JOIN (SELECT Region, County, Year, Value, Month
FROM MyTable) AS M2
PIVOT ( SUM(Value) FOR Month IN ([Jan],
[Feb], [Mar], [Apr], [May], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov],
[Dec])) AS M3
ON M1.Region = M3.Region AND M1.County = M3.County AND
M1.Year = M3.Year
ORDER BY M1.Region, M1.County, M1.Year

Regards,

Plamen Ratchev

http://www.SQLStudio.com

[markjerz@googlemail.com]

Re: Turning Rows into Columns

Thanks Plamen, much appreciated. I'm using SQL Server 2000 for this
project but for future knowledge which is the best to use (in this
case) speed/efficiency wise?

Thanks again


Plamen Ratchev wrote:

Oops, no need to SUM when using PIVOT, it should be like this:
>
SELECT DISTINCT M1.Region, M1.County, M1.Year,
[Jan],
[Feb],
[Mar],
[Apr],
[May],
[Jun],
[Jul],
[Aug],
[Sep],
[Oct],
[Nov],
[Dec]
FROM MyTable AS M1
INNER JOIN (SELECT Region, County, Year, Value, Month
FROM MyTable) AS M2
PIVOT ( SUM(Value) FOR Month IN ([Jan],
[Feb], [Mar], [Apr], [May], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov],
[Dec])) AS M3
ON M1.Region = M3.Region AND M1.County = M3.County AND
M1.Year = M3.Year
ORDER BY M1.Region, M1.County, M1.Year
>
Regards,
>
Plamen Ratchev
http://www.SQLStudio.com

[Plamen Ratchev]

Re: Turning Rows into Columns

I have not done deep analysis on performance but I would guess there will
not be much difference. It seems the PIVOT operator in SQL Server 2005
applies similar logic to the one used with the CASE technique. Using the
CASE technique is very efficient because it scans the base table only once.
You can always compare execution plans for the particular case and see which
one performs better.

The PIVOT operator is a bit confusing at first look and takes time to get
used to the syntax. One benefit is that it allows writing shorter code.

Regards,

Plamen Ratchev

http://www.SQLStudio.com