October 9, 2009 at 10:35 am
Hi,
I have a view that returns the following:
ID DateTime Parameter Value
1 01/01/2009 Parameter1 0.10
1 01/01/2009 Parameter2 0.20
2 02/01/2009 Parameter1 0.15
2 02/01/2009 Parameter2 0.30
3 03/01/2009 Parameter1 0.55
3 03/01/2009 Parameter2 0.20
I'd like to end up with a view that presents the data so:
ID DateTime Parameter1 Parameter2
1 01/01/2009 0.10 0.20
2 02/01/2009 0.15 0.30
3 03/01/2009 0.55 0.20
I'm sure a PIVOT statement could help but I'm having problems getting it right. I'd be grateful if someone could provide some help.
Thanks,
Norman
October 9, 2009 at 12:15 pm
PIVOT syntax is somewhat confusing. Take a look at the article in my signature about cross tab reports. IMO, it uses much simpler syntax and is every bit as efficient.
October 9, 2009 at 1:31 pm
I would suggest to read the article as suggested by Garadin before using this, but crosstab query would look like this,
CREATE TABLE PIVOTIN(ID int, [date] datetime, Parameter Varchar(50), [Value] float)
INSERT INTO PIVOTIN VALUES(1,'01/01/2009','Parameter1',0.10)
INSERT INTO PIVOTIN VALUES(1,'01/01/2009','Parameter2',0.20)
INSERT INTO PIVOTIN VALUES(2,'02/01/2009','Parameter1',0.15)
INSERT INTO PIVOTIN VALUES(2,'02/01/2009','Parameter2',0.30)
INSERT INTO PIVOTIN VALUES(3,'03/01/2009','Parameter1',0.55)
INSERT INTO PIVOTIN VALUES(3,'03/01/2009','Parameter2',0.20)
Select ID,DATE,
Sum(CASE WHEN Parameter = 'Parameter1' Then [Value] Else 0 END) AS Parameter1 ,
Sum(CASE WHEN Parameter = 'Parameter2' Then [Value] Else 0 END) AS Parameter2
from Pivotin
Group by ID, DATE
BTW, I should mention that I could write this because I recently went through an excellent article by Jeff Moden, http://www.sqlservercentral.com/articles/T-SQL/63681/. Thanks Jeff. (Before this Pivot was mystery for me:))
---------------------------------------------------------------------------------
October 9, 2009 at 3:14 pm
PP-564103 (10/9/2009)
I would suggest to read the article as suggested by Garadin before using this, but crosstab query would look like this,...
BTW, I should mention that I could write this because I recently went through an excellent article by Jeff Moden, http://www.sqlservercentral.com/articles/T-SQL/63681/. Thanks Jeff. (Before this Pivot was mystery for me:))
Those two aforementioned articles are one in the same ;-).
Norman,
I'd also like to point out the way that PP-564103 provides the sample data for the problem. While putting your data in code boxes is better than the way a lot of people provide sample data, the way he does it is better by far, as I can immediately copy/paste that into my environment and having working data to play with.
October 10, 2009 at 2:13 am
Those two aforementioned articles are one in the same ;-).
Ohh, sorry I just dint see that! 😉
But I dont know if I am missing something here, but this query below with PIVOT operator is returning null to me, what could be the reason?
SELECTID,
[Date],
COALESCE([1],0) as parameter1,
COALESCE([2],0) as parameter2
FROM (SELECT ID,[Date],VALUE,Parameter FROM PIVOTIN)AS TEST
PIVOT (SUM(VALUE) FOR Parameter IN ([1],[2])) AS PVT
order by id
sorry, i mean 0 after coalesce!
---------------------------------------------------------------------------------
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply