Columns in PIVOT out of order - 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 2008 Forums

Transact-SQL (2008)

Columns in PIVOT out of order

Author

Topic

risho
Starting Member

12 Posts

Posted - 2013-04-17 : 13:12:54

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-17 : 13:34:15

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,'') + ']'

risho
Starting Member

12 Posts

Posted - 2013-04-17 : 14:23:54

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-17 : 15:05:20

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,'') + ']'

risho
Starting Member

12 Posts

Posted - 2013-04-17 : 16:09:21

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources