February 6, 2012 at 12:51 pm
I am trying to turn a table that looks like this:
Year Cost Price
2009 10 100
2010 20 200
2011 30 300
Into:
2009 2010 2011
Cost 10 20 30
Price 100 200 300
I have it working to transpose the years into columns headers, and I can print one piece of data beneath each header (the cost or price) like this.
select *
from (select year, cost, price from table1) as source
PIVOT
(avg(cost)
for year in ([2009],[2010],[2011])) as Pivot
Any help is appreciated!
February 6, 2012 at 1:14 pm
You're on the right track. Just pivot in two queries and join together.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgFebruary 6, 2012 at 1:25 pm
Here is my exact query, I am havving difficulty getting one row for each attribute.
select 'cost', [2009], [2010], [2011]
from (select year, cost, price from table1) as source
PIVOT
(avg(cost)
for year in ([2009],[2010],[2011])) as Pivot
Is giving me:
2009 2010 2011
Cost 10 NULL NULL
Cost NULL 20 NULL
Cost NULL NULL 30
I tried a Group By 'Cost' after the pivot, but I get an outer reference error.
I am also thinking I would union these together instead of join to get me the 4 columns (attribute, 2009, 2010, 2011).
February 6, 2012 at 1:33 pm
Read the post in my signature on improving your answers please.
But for now....
DECLARE @testData TABLE
([Year] INT
,Cost MONEY
,Price MONEY)
INSERT @testData
(Year, Cost, Price)
VALUES
(2009, 50, 75),
(2010, 55, 81.29),
(2011, 58.5, 82.1)
SELECT
'Cost' AS [Type], [2009], [2010], [2011]
FROM
(SELECT [Year], Cost FROM @testData) AS s
PIVOT
(MAX(Cost) FOR [Year] IN ([2009], [2010], [2011]) ) as p
UNION ALL
SELECT
'Price', [2009], [2010], [2011]
FROM
(SELECT [Year], Price FROM @testData) AS s
PIVOT
(MAX(Price) FOR [Year] IN ([2009], [2010], [2011]) ) as p
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgViewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply