I need to pivot data in ss2005

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

I need to pivot data in ss2005

Author

Topic

biogem
Starting Member

13 Posts

Posted - 2009-09-15 : 12:44:42

I need to pivot the following data.
Current output is:
typename YYYY/MM Quantity
208/220V AC Power 2008/09 2
All Other 2008/09 4
ANY2 - IX Exchange 2008/09 3
BCM 2008/09 7
I need it to look like a pivot table
with the dates along the top and typename on the side and the quantity as the data.

Here is the query

SELECT typename
,CONVERT(VARCHAR(7), DATECLOSED, 111) AS [YYYY/MM],
CASE WHEN count(OnTime) IS NULL OR count(OnTime) = 0 THEN 0 ELSE count(OnTime) END as [Quantity]
from vwslareport
where dateclosed >= dateadd(day, 7 - datepart(dw, GetDate()), GetDate() - 365) --goes back 365 days and grabs data
and dateclosed <= DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))) --last day of current month
--AND TYPEOF NOT = '14'
GROUP BY typename, CONVERT(VARCHAR(7), DATECLOSED, 111)
ORDER BY CONVERT(VARCHAR(7), DATECLOSED, 111)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-15 : 13:15:10

Here is the idea. You'll have to fill in the possible column values as I started in the pivot clause. Either that or you'll have to exec a dynamic pivot.


select p.*
from   (
       SELECT typename
              ,CONVERT(VARCHAR(7), DATECLOSED, 111) AS [YYYY/MM]
              ,CASE 
              WHEN count(OnTime) IS NULL OR count(OnTime) = 0 THEN 0 
              ELSE count(OnTime) 
              END as [Quantity]
       from   vwslareport
       where  dateclosed >= dateadd(day, 7 - datepart(dw, GetDate()), GetDate() - 365) --goes back 365 days and grabs data
       and    dateclosed <= DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))) --last day of current month
       GROUP BY typename, CONVERT(VARCHAR(7), DATECLOSED, 111)
       ) d
pivot (
       sum(quantity) for [YYYY/MM] in ([2009/09],[2009/08] /*, etc...*/ )
      ) p

Be One with the Optimizer
TG

biogem
Starting Member

13 Posts

Posted - 2009-09-15 : 13:28:25

Thanks, that works for sql server 2008 is there any way to use something like this for sql server 2005?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-15 : 13:30:42

That works for 2005 as well. If it isn't working for you check your database compatibility level. Make sure it's 90

Be One with the Optimizer
TG

biogem
Starting Member

13 Posts

Posted - 2009-09-15 : 13:30:54

Here is the error message.
Msg 325, Level 15, State 1, Line 27
Incorrect syntax near 'pivot'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-15 : 13:53:27

So based on my last comment and that error message are you clear on what the problem is?

Be One with the Optimizer
TG

biogem
Starting Member

13 Posts

Posted - 2009-09-15 : 13:59:11

Interesting, we have all but one server set to 90. And of course it is the one I need to query.
Thanks for you help TG.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-15 : 14:07:18