SQLSERVER 2008 : Rows into Comma seperated values

  • declare @year int = 2013,

    @week int = 27

    declare @dte date

    select @dte = dateadd(week, @week - 1, dateadd(year, @year - 1900, 0))

    SELECT *

    FROM

    (

    SELECT [DATE] = DATEADD(DAY, n, dateadd(day, (datediff(day, '17530107', @dte) / 7) * 7, '17530107'))

    FROM (

    VALUES (0), (1), (2), (3), (4), (5), (6)

    ) num (n)

    ) d

    WHERE [DATE] >= dateadd(year, @year - 1900, 0)

    AND [DATE] <= dateadd(year, @year - 1900 + 1, -1)

    my output needs to be as follows

    [2013-06-30],[2013-07-01],[2013-07-02],[2013-07-03],[2013-07-04],[2013-07-05],[2013-07-06]

    note : i need the symbol [ ] as well.

    can any one help me in this

  • This is when you use FOR XML PATH(''). A completely obscure syntax for the purpose, but it works.

    declare @year int = 2013,

    @week int = 27

    declare @dte date

    select @dte = dateadd(week, @week - 1, dateadd(year, @year - 1900, 0))

    ; WITH dates AS (

    SELECT [DATE] = DATEADD(DAY, n, dateadd(day, (datediff(day, '17530107', @dte) / 7) * 7, '17530107'))

    FROM (VALUES (0), (1), (2), (3), (4), (5), (6) ) num (n)

    ), commalist (datelist) AS (

    SELECT (SELECT quotename(convert(date, DATE)) + ','

    FROM dates

    WHERE [DATE] >= dateadd(year, @year - 1900, 0)

    AND [DATE] <= dateadd(year, @year - 1900 + 1, -1)

    ORDER BY DATE

    FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')

    )

    SELECT substring(datelist, 1, len(datelist) - 1)

    FROM commalist

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • thanks a lot gentle man.

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

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