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 2008 Forums
 Transact-SQL (2008)
 Columns in PIVOT out of order
 New Topic  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

3327 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

3327 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  
 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.05 seconds. Powered By: Snitz Forums 2000