ordering dynamic column string

  • 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

  • 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 😀

  • perfect thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply