Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

ordering dynamic column string Expand / Collapse
Author
Message
Posted Thursday, December 5, 2013 6:06 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 14, 2014 8:25 AM
Points: 116, Visits: 402
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
Post #1519995
Posted Thursday, December 5, 2013 6:19 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 5:32 AM
Points: 624, Visits: 1,129
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
Post #1520006
Posted Thursday, December 5, 2013 7:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 14, 2014 8:25 AM
Points: 116, Visits: 402
perfect thanks
Post #1520068
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse