SQL - Convert results into comma separated string

Question

Using another article on StackOverflow (How to split date ranges based on months in SQL Server 2005) I have run a SELECT statement which splits up a date range by month and returns 2 columns (DateFrom, DateTo) in SQL.

DECLARE @SDate DATE = '2012/08/01'
DECLARE @EDate DATE = '2013/09/01'

SELECT 
    DATEADD(MONTH, n.Number, @SDate) as DateFrom, 
    DATEADD(day, -1, DATEADD(MONTH, n.Number, DATEADD(YEAR,-1,@EDate))) as DateTo
FROM 
    master.dbo.spt_values n
WHERE 
    n.Number < DATEDIFF(MONTH, @SDate, @EDate)
    AND n.Type = 'P'

I now need to add each of the returned rows into a string separating the columns by | (Dateto|Datefrom) and each row by ,.

For example if your run the code above the result would be (just for this example im only using the first 4 rows but I need all of them in one string):

R | Date From  |  Date To
1 | 2012-08-01 | 2012-08-31
2 | 2012-09-01 | 2012-09-30
3 | 2012-10-01 | 2012-10-30
4 | 2012-11-01 | 2012-11-30

Code:

DECLARE @stralldates VarChar(MAX)
/* SET @stralldates = INSERTCODE */
PRINT @stralldates

What I need PRINT to return:

2012-08-01|2012-08-31,2012-10-01|2012-10-30,2012-10-01|2012-10-30,2012-11-01|2012-11-30

I have tried several suggestions from other similar questions on StackOverflow (such as CONCAT) with no success.

Any help or suggestions would be appreciated.

score 3 · Accepted Answer · answered Aug 07 '13 at 09:39

set @StrAllDates = 
  stuff((select ','+convert(char(10), SDate, 121)+'|'+convert(char(10), dateadd(day, -1, dateadd(month, 1, SDate)), 121)
         from (
              select dateadd(month, n.number, @SDate) as SDate
              from master..spt_values as n
              where n.number < datediff(month, @SDate, @EDate) and
                    n.type = 'P'
              ) as T
         order by SDate
         for xml path('')), 1, 1, '')

score 1 · Answer 2 · answered Aug 07 '13 at 09:36

1

I only know Oracle which provides wm_concat (undocumented) or listagg depending on version.

answered Aug 07 '13 at 09:36

siddharth · Answer 3 · 2013-08-07T10:05:10.063

1

You can use the below script to get the data in the format you want to. It basically uses the coalesce method for concatenanation. I created a temp table to hold the data which is then used to iterate over the rows.

DECLARE @SDate DATE = '2012/08/01'
DECLARE @EDate DATE = '2013/09/01'
SELECT 
    DATEADD(MONTH, n.Number, @SDate) as DateFrom, 
    DATEADD(day, -1, DATEADD(MONTH, n.Number, DATEADD(YEAR,-1,@EDate))) as DateTo
INTO #tmp_data
FROM master.dbo.spt_values n
WHERE 
n.Number < DATEDIFF(MONTH, @SDate, @EDate)
AND n.Type = 'P'

declare @my_string varchar(8000);
select @my_string = 
    coalesce( @my_string + ',', '') 
    + convert(varchar(10), d.DateFrom, 126) 
    + '|' + convert(varchar(10), d.DateTo, 126)  
from #tmp_data d
order by d.DateFrom
select @my_string

drop table #tmp_data

Also, if you want to use the row rumber, you can use use it by adding another column to the select clause - ROW_NUMBER() OVER(ORDER BY n.Number) as RowNum

edited Aug 07 '13 at 10:05

answered Aug 07 '13 at 09:53

siddharth

660
2
6
18

This answer does not guarantee that dates are concatenated in ascending date order. Only OP will know if that is a problem or not. – Mikael Eriksson Aug 07 '13 at 09:59
@Mikael Eriksson I didn't realise that you could only mark one answer, I'm guessing that using temptables wouldn't be as efficient anyway. But thanks for your help siddharth! – Ben Barter Aug 07 '13 at 10:00
Thanks Ben. @MikaelEriksson - We can always add an order by clause when selecting from the temp table. – siddharth Aug 07 '13 at 10:04
Concatenating strings like this is undocumented and the correct behavior is undefined even with an order by clause. [Why SQL Server Ignores vaules in string concatenation when ORDER BY clause specified](http://stackoverflow.com/questions/5538187/why-sql-server-ignores-vaules-in-string-concatenation-when-order-by-clause-speci) – Mikael Eriksson Aug 07 '13 at 10:19

score 1 · Answer 4 · answered Aug 07 '13 at 10:45

The following query will give you the required comma seperated string:

DECLARE @Begin DATETIME
DECLARE @End DATETIME
Declare @test Table(startDate datetime, endDate datetime)
DECLARE @listStr VARCHAR(MAX)
SELECT @Begin = '20110101', @End = '20120101'

Insert into @test
SELECT DATEADD(MONTH, n.Number, @Begin) DateFrom, DATEADD(day, -1, DATEADD(MONTH, n.Number+1, @Begin)) DateTo
FROM master.dbo.spt_values n
WHERE 
    n.Number <= DATEDIFF(MONTH, @begin, @end)
AND n.Type = 'P'

select  @listStr =  COALESCE(@listStr+',' ,'')+ CONVERT(VARCHAR(10), startDate, 120) + '|' + CONVERT(varchar(10),endDate,120) from @test 
select @listStr

SQL - Convert results into comma separated string

4 Answers4