Transpose Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Transpose Query

Author

Topic

bmsra79
Starting Member

24 Posts

Posted - 2012-09-13 : 13:15:18

I need to do transpose in SQL Server 2008 and my data is like below.
QTR, YEAR, CLASS, TOTALS
1, 2010, 1, 250
2, 2010, 1, 350
3, 2010, 1, 250
4, 2010, 1, 350
1, 2011, 2, 250
2, 2011, 2, 350
3, 2011, 2, 65
4, 2011, 2, 350

Result Expected:
YEAR, CLASS, QTR-1, QTR-2, QTR-3, QTR-4
2010, 1, 250, 350, 250, 350
2011, 2, 250, 350, 65, 350

Any suggestions are most welcome.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-13 : 13:19:24

[code]
SELECT [YEAR],
[CLASS],
[1] AS [QTR-1],
[2] AS [QTR-2],
[3] AS [QTR-3],
[4] AS [QTR-4]
FROM YourTable
PIVOT (SUM(TOTALS) FOR QTR IN ([1],[2],[3],[4]))p
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bmsra79
Starting Member

24 Posts

Posted - 2012-09-13 : 13:28:27

I am actually using query to derive these fields
So when I use your logic like below:
SELECT [YEAR],
[CLASS],
[1] AS [QTR-1],
[2] AS [QTR-2],
[3] AS [QTR-3],
[4] AS [QTR-4]
FROM
(
select QTR, YEAR, CLASS, TOTALS from TableA
UNION
select QTR, YEAR, CLASS, TOTALS from TableB
UNION
select QTR, YEAR, CLASS, TOTALS from TableC
)
PIVOT (SUM(TOTALS) FOR QTR IN ([1],[2],[3],[4]))p

It gives me an error
Incorrect syntax near the keyword 'PIVOT'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-13 : 13:33:15

quote:
Originally posted by bmsra79

I am actually using query to derive these fields
So when I use your logic like below:
SELECT [YEAR],
[CLASS],
[1] AS [QTR-1],
[2] AS [QTR-2],
[3] AS [QTR-3],
[4] AS [QTR-4]
FROM
(
select QTR, YEAR, CLASS, TOTALS from TableA
UNION
select QTR, YEAR, CLASS, TOTALS from TableB
UNION
select QTR, YEAR, CLASS, TOTALS from TableC
)t
PIVOT (SUM(TOTALS) FOR QTR IN ([1],[2],[3],[4]))p

It gives me an error
Incorrect syntax near the keyword 'PIVOT'.

you missed an alias for derived table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bmsra79
Starting Member

24 Posts

Posted - 2012-09-13 : 13:38:51

It works as expected. Thank You.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-13 : 13:41:39

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources