Select using PIVOT - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Select using PIVOT

Author

Topic

darms21
Yak Posting Veteran

54 Posts

Posted - 2012-09-21 : 14:51:45

I have the following query and am attempting to turn it into a pivot table:

SELECT     CASE WHEN RefreshStatus = 'Delivered' THEN 'Delivered to date' ELSE refreshstatus END AS [Refresh Status], COUNT(refreshstatus) AS Count
FROM         refresh_report_status_tracker
GROUP BY refreshstatus

This returns:
RefreshStatus Count
Contacted 13
Build 20
ReadyToBuild 14

I want it to return:
RefreshStatus Contactactd Build ReadyToBuild
Count 13 20 14

and I think that a PIVOT is the way to go.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-21 : 16:11:50

[code]
SELECT *
FROM (SELECT CASE WHEN RefreshStatus = 'Delivered' THEN 'Delivered to date' ELSE refreshstatus END AS [Refresh Status],
COUNT(*) AS [Count]
FROM table
GROUP BY CASE WHEN RefreshStatus = 'Delivered' THEN 'Delivered to date' ELSE refreshstatus END )t
PIVOT (SUM([Count]) FOR RefreshStatus IN ([Contacted], [Build] ,[ReadyToBuild]))p
[/code]

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

darms21
Yak Posting Veteran

54 Posts

Posted - 2012-09-24 : 12:58:56

Msg 207, Level 16, State 1, Line 6
Invalid column name 'RefreshStatus'.

I don't know the PIVOT function well, any idea why that error is?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-24 : 13:32:29

quote:
Originally posted by darms21

Msg 207, Level 16, State 1, Line 6
Invalid column name 'RefreshStatus'.

I don't know the PIVOT function well, any idea why that error is?

it was a typo


SELECT *
FROM (SELECT CASE WHEN RefreshStatus = 'Delivered' THEN 'Delivered to date' ELSE refreshstatus END AS [Refresh Status], 
COUNT(*) AS [Count]
FROM table
GROUP BY CASE WHEN RefreshStatus = 'Delivered' THEN 'Delivered to date' ELSE refreshstatus END )t
PIVOT (SUM([Count]) FOR [Refresh Status] IN ([Contacted], [Build] ,[ReadyToBuild]))p

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources