SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Select using PIVOT
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

darms21
Yak Posting Veteran

54 Posts

Posted - 09/21/2012 :  14:51:45  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 09/21/2012 :  16:11:50  Show Profile  Reply with Quote

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


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

Go to Top of Page

darms21
Yak Posting Veteran

54 Posts

Posted - 09/24/2012 :  12:58:56  Show Profile  Reply with Quote
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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 09/24/2012 :  13:32:29  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000