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 2005 Forums
 Transact-SQL (2005)
 ORDER BY Problem when using FOR XML PATH()
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vision.v1
Yak Posting Veteran

71 Posts

Posted - 05/17/2013 :  11:13:11  Show Profile  Reply with Quote
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


Edited by - vision.v1 on 05/17/2013 11:17:08

James K
Flowing Fount of Yak Knowledge

3724 Posts

Posted - 05/17/2013 :  12:05:14  Show Profile  Reply with Quote
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
Go to Top of Page

vision.v1
Yak Posting Veteran

71 Posts

Posted - 05/17/2013 :  13:11:02  Show Profile  Reply with Quote
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



Edited by - vision.v1 on 05/17/2013 13:11:32
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.03 seconds. Powered By: Snitz Forums 2000