xml Path query ORDER BY issue - 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)

xml Path query ORDER BY issue

Author

Topic

nietzky
Yak Posting Veteran

75 Posts

Posted - 2012-04-11 : 13:29:01

I need to order by weekno2 to show weeks starting from the beginning of the year. The sp issues the following error with order by weekno2 clause. How can i sort these values? If I remove "order by" sp works but the list is not sorted so weeks are showing up as ramndom values ex.

VolPath |08|04|06|12|

Msg 145, Level 15, State 1, Procedure sp_Test, Line 10
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

weekno2 is varchar ex. 01 - first week, 02,03,04,...,10,11,12 and so on.

ALTER PROCEDURE [dbo].[sp_Test]

AS
BEGIN
SET NOCOUNT ON;
DECLARE @YrWkList varchar(1000),@sql varchar(4000)
select @YrWkList = stuff((select distinct '],['+ weekno2 from dbo.Testview order by weekno2 for xml path('')),1,2,'') + ']'

set @sql='select *
from
(
select VolPath, AVG([%Used])as AvgPercentUsed, weekno2 from dbo.Testview
group by VolPath, weekno2
)m
pivot (max(AvgPercentUsed) for weekno2 in (' + @YrWkList + '))p'

exec (@sql)

END

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-11 : 14:58:45

Change the order by clause to

'],['+ weekno2

instead of just weekno2.

You can use the QUOTENAME function instead of inserting the square brackets yourself, as in

stuff((select distinct ','+ QUOTENAME(weekno2) from dbo.Testview order by ','+ QUOTENAME(weekno2) for xml path('')),1,1,'')

nietzky
Yak Posting Veteran

75 Posts

Posted - 2012-04-11 : 15:04:59

Thank you very much used QUOTENAME approach you provided.
Works great.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-11 : 15:20:37

very welcome .)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources