December 5, 2013 at 6:06 am
hi
i am trying to create a set of dynamic date columns (year - monthname). When i create the temp table of distinct dates and sort it's all fine until i then create the dynamic string in which case it sorts alphabetically.
i need the columns to be sorted by year, month but it's sorting by year, monthname. so the 'select @cols' is not sorting correctly.
here's some code to use as an example:
if object_id('tempdb..#dateTable') is not null
drop table #dateTable
if object_id('tempdb..#distinctDate') is not null
drop table #distinctDate
create table #dateTable(
Year int,
MonthNumber int,
MonthName Varchar(3)
)
insert into #dateTable
select 2013, 1, 'Jan' union
select 2013, 2, 'Feb' union
select 2013, 3, 'Mar' union
select 2013, 4, 'Apr' union
select 2013, 5, 'May' union
select 2013, 6, 'Jun' union
select 2013, 7, 'Jul' union
select 2013, 8, 'Aug' union
select 2013, 9, 'Sep' union
select 2013, 10, 'Oct' union
select 2013, 11, 'Nov' union
select 2013, 12, 'Dec' union
select 2014, 1, 'Jan' union
select 2014, 2, 'Feb' union
select 2014, 3, 'Mar' union
select 2014, 4, 'Apr' union
select 2014, 5, 'May' union
select 2014, 6, 'Jun' union
select 2014, 7, 'Jul' union
select 2014, 8, 'Aug' union
select 2014, 9, 'Sep' union
select 2014, 10, 'Oct' union
select 2014, 11, 'Nov' union
select 2014, 12, 'Dec'
-- create dynamic date range columns
select distinct Year, MonthNumber, convert(varchar(4), Year) + ' - ' + MonthName as Date
into #distinctDate
from #dateTable
order by Year, MonthNumber
select * from #distinctDate
--convert(CHAR(10), Date, 120)
-- create the dynamic date columns. (weeks of the year)
declare @cols AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Date)
FROM #distinctDate
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
select @cols
can i sort in this way? thanks
December 5, 2013 at 6:19 am
Use the following query
select @cols = STUFF((SELECT ',' + QUOTENAME(Date)
FROM #distinctDate
Order by [year], MonthNumber
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,''
)
I have remove the distinct, if you want to distinct the record to it when you create the #distinctDate
after that i have added the order by clause, because there is no such thing as Natural Order 😀
December 5, 2013 at 7:29 am
perfect thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply