March 3, 2012 at 1:20 am
Hi,
I had table like below say 'TestTable'. In this table Ratio column value is Data1/Data2
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.
So what all i want is [Ratio] column as [Jan2012],[Feb2012] etc i.e Pivot based on [Year] and [Month] columns and not only month.
Please Help
Thanks!
March 3, 2012 at 2:03 am
Step 1 would be to use datetime column instead of separate columns for year and moth.
Then you'd be able to use PIVOT. Not just for PIVOT but as a better solution in general (unless there's a buch of constraints to make sure both columns are valid (e.g. Month only allows values from 1 to 12). The constraint would become slightly moredifficult if you'll need to check that there are no dates larger than today.
Regardless of the table design, my preferred method would be the CrossTab method as described in the related link in my signature.
March 3, 2012 at 3:36 am
Thanks a lot for your help and cross tab article was really helpful.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply