Query for PIVOT Table - 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

General SQL Server Forums

New to SQL Server Programming

Query for PIVOT Table

Author

Topic

snagar
Starting Member

14 Posts

Posted - 2012-03-02 : 13:50:35

Hi,
I had table like below say 'TestTable'
Year Month Data1 Data2 Ratio
-----------------------------
2011 12 2 1 2
2012 1 3 2 1.5
2012 1 6 2 3
2012 2 5 2 2.5

what all i want is result as below:
Data1 Data2 Dec2011 Jan2012 Feb2012
---------------------------------------------
2 1 2 Null Null
3 2 Null 4.5 Null
2 5 Null Null 2.5

I had written below query for this-

SELECT [Data1],[Data2],[1] AS Jan,[2] As Feb,
[3] As Mar,[4] As Apr,[5] As May,[6] As Jun,[7] As Jul,

As Aug,[9] As Sep,
[10] AS Oct,[11] As Nov,[12] As Dec
FROM (SELECT * FROM TestTable)T1
PIVOT
(
SUM(Ratio)
FOR
[Month] IN([1],[2],[3],[4],[5],[6],[7],

,[9],[10],[11],[12])

) AS P1

But using this query i am only able to get data based on row value from Month whereas i want data from combination of row value of Year and Month like Jan2012 and not only Jan.

Please Help
Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-02 : 15:10:12

how do you want final output to come? How will Data1,data2 values get split up?

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

snagar
Starting Member

14 Posts

Posted - 2012-03-02 : 23:36:48

quote:
Originally posted by visakh16

how do you want final output to come? How will Data1,data2 values get split up?

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

Data1 column contains some numeric value and Data2 column contains some numeric value.
Ratio column value is Data1/Data2

Now instead of Ratio Column i want Summation of Ratio Grouped By Year and Month i.e Pivoting based on two columns.
And those new columns will be named like [Jan2012],[Feb2012] etc.

I had achieved pivoting based on Month Column as in Query Posted above.
So is it possible to achieve pivoting based on two columns i.e both month and year.

Below is Sample Result data for Jan 2012 data that i want-

Data1-- Data2-- Jan2012-- Feb2012
1--------1---------1---------Null
2--------1---------2---------Null
3--------1---------3---------Null

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources