ORDER BY Problem when using FOR XML PATH()

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 2005 Forums

Transact-SQL (2005)

ORDER BY Problem when using FOR XML PATH()

Author

Topic

vision.v1
Yak Posting Veteran

72 Posts

Posted - 2013-05-17 : 11:13:11

Hi,

I am getting output for the below query as [Mar - 2013],[Jan - 2013],[Feb - 2013]

instead i would like to get the ordered output as below:

[Jan - 2013],[Feb - 2013],[Mar - 2013]

bacause based on the above output i need to use as dynamic column name of PIVOT.

Please advise.

IF OBJECT_ID('tempdb.dbo.#temp') IS NOT NULL
DROP TABLE #temp
CREATE TABLE #temp
(
[month] INT,
[year] INT,
[monthAndYear] VARCHAR(50)
)

INSERT #temp
SELECT MONTH('01/01/2013'),YEAR('01/01/2013'),LEFT(CAST(DATENAME(MONTH,'01/01/2013') AS VARCHAR),3) + ' - ' + CAST(YEAR('01/01/2013') AS VARCHAR)

INSERT #temp
SELECT MONTH('02/01/2013'),YEAR('02/01/2013'),LEFT(CAST(DATENAME(MONTH,'02/01/2013') AS VARCHAR),3) + ' - ' + CAST(YEAR('02/01/2013') AS VARCHAR)

INSERT #temp
SELECT MONTH('03/01/2013'),YEAR('03/01/2013'),LEFT(CAST(DATENAME(MONTH,'03/01/2013') AS VARCHAR),3) + ' - ' + CAST(YEAR('03/01/2013') AS VARCHAR)

SELECT * FROM #temp

DECLARE @monthAndYear VARCHAR(MAX)
SELECT @monthAndYear = STUFF(( SELECT DISTINCT
'],[' + CAST(monthAndYear AS VARCHAR)
FROM #temp
ORDER BY '],[' + CAST(monthAndYear AS VARCHAR) DESC
FOR XML PATH('')
), 1, 2, '') + ']'

SELECT @monthAndYear

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-17 : 12:05:14

Order it by the year and month rather than the character string. Also, when you use VARCHAR data type, always specify a length, (e.g. VARCHAR(32) rather than just VARCHAR). In this case, there is no need to do the cast, because the column is already varchar(50).


DECLARE @monthAndYear VARCHAR(MAX);
SELECT @monthAndYear = STUFF((
SELECT '],[' + [monthAndYear] FROM #temp ORDER BY year, month FOR XML PATH('')),1,2,'');
SELECT @monthAndYear

vision.v1
Yak Posting Veteran

72 Posts

Posted - 2013-05-17 : 13:11:02

Thanks, it worked for me with minor change

quote:
Originally posted by James K

Order it by the year and month rather than the character string. Also, when you use VARCHAR data type, always specify a length, (e.g. VARCHAR(32) rather than just VARCHAR). In this case, there is no need to do the cast, because the column is already varchar(50).

DECLARE @monthAndYear VARCHAR(MAX);
SELECT @monthAndYear = STUFF((
SELECT '],[' + [monthAndYear] FROM #temp ORDER BY year, month FOR XML PATH('')),1,2,'');
SELECT @monthAndYear

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources