PIVOTing dates in the correct order

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)

PIVOTing dates in the correct order

Author

Topic

Ambulare
Starting Member

8 Posts

Posted - 2011-02-03 : 11:35:09

Hi all,

I'm kinda tearing my hair out trying to get the PIVOT command to work using dates in the FOR variable IN(....) clause.

I need to use dynamic SQL to create the column names as the dates will change every day, but nothing I do seems to stop the dates being ordered randomly (or alphabetically if I convert them to varchars) instead of in correct order.

I'm creating a variable containing the column headings using

SELECT @columns = COALESCE(@columns + ',[' + cast(Variable as varchar) + ']',
'[' + cast(Variable as varchar)+ ']')
FROM #temp123
GROUP BY Variable

but this always ends up with [April 01 2010 00:00], [August 01 2010 00:00]... instead of [April 01 2010 00:00], [May 01 2010 00:00]. Since TSQL won't let you add a sort clause in a nested query, I can't sort the dates in the query that creates the @columns variable.

Has anyone successfully achieved this and could you help me out?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-03 : 15:17:21

If the dates are in the table in the correct order, it works. You can't create another table from the #temp123 which orders them?


 
SELECT DISTINCT dateadd(m,number,'20100101') as vdate
into #dates
FROM master.dbo.spt_values
Where number between 0 and 11
ORDER BY dateadd(m,number,'20100101') asc


Declare @columns varchar(max)

SELECT @columns = COALESCE(@columns + ',[' + convert(varchar(12),vdate,107) + ']',
'[' + convert(varchar(12),vdate,107)+ ']')
FROM #dates
GROUP BY vdate

Print @columns

Maybe if you order the #temp123 table by the Variable column...
/*Result string

[Jan 1 2010 12:00AM],[Feb 01, 2010],[Mar 01, 2010],[Apr 01, 2010],
[May 01, 2010],[Jun 01, 2010],[Jul 01, 2010],[Aug 01, 2010],[Sep 01, 2010],
[Oct 01, 2010],[Nov 01, 2010],[Dec 01, 2010]

*/

I suggest this instead using convert(varchar(12),vdate,107) to strip of the empty times instead like I did above.

I am also assuming that the select statement you show is nested somewhere since you can't add the order by..

Poor planning on your part does not constitute an emergency on my part.

Ambulare
Starting Member

8 Posts

Posted - 2011-02-04 : 10:04:30

Thanks for your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources