Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

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

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Columns in PIVOT out of order
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

risho
Starting Member

12 Posts

Posted - 04/17/2013 :  13:12:54  Show Profile  Reply with Quote
I've got a sproc with a pivot where my columns are out of order and was wondering if someone can help me figure out how to correct this.

The stored procedure looks like this:

DECLARE @cols NVARCHAR(MAX)
DECLARE @query NVARCHAR(MAX)
	
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT '],[' + [Description] 
FROM dbo.vQualScoringGrade 
ORDER BY '],[' + [Description] FOR XML PATH('')), 1,2,'') + ']'
    
SET @query = N'SELECT [Fee Period], ' + @cols + ' FROM 
(SELECT [Award Fee Period],GradeCount, Description, Value, OrderBy FROM 
dbo.vQualScoringGrade) p
PIVOT (SUM([Value]) for [Description] IN ( ' + @cols + ' )) AS pvt ORDER BY [Fee Period]'
    
execute(@query)


The view [vQualScoringGrade] that feeds the sproc looks like this:

SELECT TOP (100) PERCENT [Fee Period], GradeCount, Description, 
GradeCount AS Value, OrderBy
FROM (SELECT [Fee Period], 'No of Interim' AS Description, COUNT(VerNumber) AS GradeCount, 1 AS OrderBy
FROM dbo.vQualScoringExcellent AS vQualScoringExcellent
GROUP BY GIIANumber, Description
UNION
SELECT [Fee Period], 'Excellent' AS Description, COUNT(Description) AS GradeCount, 2 AS OrderBy
FROM dbo.vQualScoringExcellent AS vQualScoringExcellent_1
GROUP BY VerNumber, Description
UNION
SELECT [Fee Period], 'Very Good' AS Description, COUNT(Description) AS GradeCount, 3 AS OrderBy
FROM dbo.vQualScoringVeryGood AS vQualScoringVeryGood
GROUP BY VerNumber, Description
UNION
SELECT [Fee Period], 'Good' AS Description, COUNT(Description) AS GradeCount, 4 AS OrderBy
FROM dbo.vQualScoringGood AS vQualScoringGood
GROUP BY VerNumber, Description
UNION
SELECT [Fee Period], 'Satisfactory' AS Description, COUNT(Description) AS GradeCount, 5 AS OrderBy
FROM dbo.vQualScoringSatisfactory AS vQualScoringSatisfactory
GROUP BY VerNumber, Description
UNION
SELECT [Fee Period], 'Unsatisfactory' AS Description, COUNT(Description) AS GradeCount, 6 AS OrderBy
FROM dbo.vQualScoringUnsatisfactory AS vQualScoringUnsatisfactory
GROUP BY VerNumber, Description) AS QualScoringGrade
ORDER BY OrderBy


So presently the columns appear from left to right in the following order:
[Fee Period], [Excellent], [Good], [No of Interim], [Satisfactory], [Unsatisfactory], [Very Good].

Instead I need the following order:
[Fee Period], [No of Interim], [Excellent], [Very Good],[Good],[Satisfactory], [Unsatisfactory].

Is there a way to make this happen? Thanks.

EJM

James K
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 04/17/2013 :  13:34:15  Show Profile  Reply with Quote
Order the query where you are constructing the comma-separated list. You can do what I am showing below, but I guess that would defeat the purpose of having a dynamic query. Is there another column that you can use for ordering? If there is use that column.
SELECT @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT '],[' + [Description] 
FROM dbo.vQualScoringGrade 
ORDER BY '],[' + 
	CASE 
		WHEN [Description]= 'Fee Period' THEN 0
		WHEN [Description]= 'No Interim' THEN 1
		WHEN [Description]= 'Excellent' THEN 2
		-- etc
	END  FOR XML PATH('')), 1,2,'') + ']'
Go to Top of Page

risho
Starting Member

12 Posts

Posted - 04/17/2013 :  14:23:54  Show Profile  Reply with Quote
quote:
Originally posted by James K

Is there another column that you can use for ordering?



There is, I call it "OrderBy" and with each query I assing a numerical value between 1 and 6. I've tried to change my query in the sproc but I was under the impression that entire line "ORDER BY '],[' + [Description] FOR XML PATH('')), 1,2,'') + ']'" is needed to build the colums. When I change that by adding a my sort column, it breaks.

EJM

Edited by - risho on 04/17/2013 14:25:02
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 04/17/2013 :  15:05:20  Show Profile  Reply with Quote
Is it complaining about syntax error? Can you try this?
SELECT @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT '],[' + [Description] 
FROM dbo.vQualScoringGrade 
ORDER BY -- '],[' + 
	CASE 
		WHEN [Description]= 'Fee Period' THEN 0
		WHEN [Description]= 'No Interim' THEN 1
		WHEN [Description]= 'Excellent' THEN 2
		-- etc
	END  FOR XML PATH('')), 1,2,'') + ']'

Edited by - James K on 04/17/2013 15:05:37
Go to Top of Page

risho
Starting Member

12 Posts

Posted - 04/17/2013 :  16:09:21  Show Profile  Reply with Quote
This is the solution:
SELECT @cols = STUFF(( SELECT '],[' + [Description] FROM dbo.vQualScoringGrade
GROUP BY [Description]
ORDER BY
MAX([orderby]), [Description]
FOR XML PATH('')), 1,2,'') + ']'

Thanks for your help!

EJM

Edited by - risho on 04/17/2013 16:11:10
Go to Top of Page
  Previous Topic Topic Next 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.05 seconds. Powered By: Snitz Forums 2000