I have a question about SQL Server 2008: how to concatenate with comma (,) for multiple columns with orders of columns?

Table timeschedule:

Id |  Name | days |daysfrequency|scheduletime
---+-------+------+-------------+------------
1  |  a    |Day4  |Monthly      |22:10
1  |  a    |Thu   |Weekly       |07:30
1  |  a    |Fri   |Daily        |23:10
2  |  b    |Mon   |Weekly       |20:00
2  |  b    |Tue   |Weekly       |23:10
2  |  b    |Wed   |Weekly       |18:10
2  |  b    |Thu   |Weekly       |10:23
2  |  b    |Fri   |Weekly       |1:23

Based on this data, I need an output like this:

id | Name  | Days              |DaysFrequency        | ScheduleTIme
---+-------+-------------------+---------------------+------------------
1  |a      |Days4,THu,Fri      |Monthly,Weekly,Daily |22:10,07:30,23:10
2  |b      |Mon,Tue,Wed,Thu,Fri|Weekly               |20:00,23:10,18:10,10:23,1:23

I have tried with this SQL:

select 
    id, name,
    Days = stuff((select distinct ',' days 
                  from timeschedule t
                  where t.id = t1.id and t.name = t1.name
                  for xml path('')), 1, 1, ''),
    daysfrequency = stuff((select distinct ',' daysfrequency 
                           from timeschedule t
                           where t.id = t1.id and t.name = t1.name
                           for xml path('')), 1, 1, ''),
    scheduletime = stuff((select distinct ',' scheduletime 
                          from timeschedule t
                          where t.id = t1.id and t.name = t1.name
                          for xml path('')), 1, 1, '')
from 
    timeschedule t1 
group by 
    id, name

Using this query it is not return the expected order in the days, daysfrequenc, scheduletime output columns.

Could someone please tell me how to write query to achieve this task in SQL Server 2008?

I have a question about SQL Server 2008: how to concatenate with comma (,) for multiple columns with orders of columns?

Table timeschedule:

Id |  Name | days |daysfrequency|scheduletime
---+-------+------+-------------+------------
1  |  a    |Day4  |Monthly      |22:10
1  |  a    |Thu   |Weekly       |07:30
1  |  a    |Fri   |Daily        |23:10
2  |  b    |Mon   |Weekly       |20:00
2  |  b    |Tue   |Weekly       |23:10
2  |  b    |Wed   |Weekly       |18:10
2  |  b    |Thu   |Weekly       |10:23
2  |  b    |Fri   |Weekly       |1:23

Based on this data, I need an output like this:

id | Name  | Days              |DaysFrequency        | ScheduleTIme
---+-------+-------------------+---------------------+------------------
1  |a      |Days4,THu,Fri      |Monthly,Weekly,Daily |22:10,07:30,23:10
2  |b      |Mon,Tue,Wed,Thu,Fri|Weekly               |20:00,23:10,18:10,10:23,1:23

I have tried with this SQL:

select 
    id, name,
    Days = stuff((select distinct ',' days 
                  from timeschedule t
                  where t.id = t1.id and t.name = t1.name
                  for xml path('')), 1, 1, ''),
    daysfrequency = stuff((select distinct ',' daysfrequency 
                           from timeschedule t
                           where t.id = t1.id and t.name = t1.name
                           for xml path('')), 1, 1, ''),
    scheduletime = stuff((select distinct ',' scheduletime 
                          from timeschedule t
                          where t.id = t1.id and t.name = t1.name
                          for xml path('')), 1, 1, '')
from 
    timeschedule t1 
group by 
    id, name

Using this query it is not return the expected order in the days, daysfrequenc, scheduletime output columns.

Could someone please tell me how to write query to achieve this task in SQL Server 2008?

• sql
• sql-server
• sql-server-2008

• 1 What is the logic behind the ordering? – Tim Biegeleisen Commented Nov 20, 2024 at 3:16
• 1 You need another column in the table to determine the required ordering – Squirrel Commented Nov 20, 2024 at 3:23
• 6 Upgrade this very obsolete version and then use STRING_AGG – Jonas Metzler Commented Nov 20, 2024 at 6:11
• Does this answer your question? String_agg for SQL Server before 2017 – Thom A Commented Nov 20, 2024 at 9:36
• This stuff will never work properly in a real world. Let's say you have another schedule for Weekly Fri. How are you gonna display that? The "helpful" schedule table is just a recipe for a misleading one. – siggemannen Commented Nov 20, 2024 at 10:56

2 Answers 2

Please try the following solution(s).

As correctly mentioned by @Squirrel:

You need another column in the table to determine the required ordering

SQL #1

-- DDL and sample data population, start
DECLARE @tbl TABLE (id INT, _name CHAR(1), days VARCHAR(10), daysfrequency VARCHAR(10), scheduletime VARCHAR(10));
INSERT INTO @tbl (id,_name,days,daysfrequency,scheduletime) VALUES
(1, 'a', 'Day4','Monthly','22:10'),
(1, 'a', 'Thu', 'Weekly', '07:30'),
(1, 'a', 'Fri', 'Daily',  '23:10'),
(2, 'b', 'Mon', 'Weekly', '20:00'),
(2, 'b', 'Tue', 'Weekly', '23:10'),
(2, 'b', 'Wed', 'Weekly', '18:10'),
(2, 'b', 'Thu', 'Weekly', '10:23'),
(2, 'b', 'Fri', 'Weekly', '1:23');
-- DDL and sample data population, end

-- Method #1
SELECT id, _name 
    , Days=STUFF((SELECT DISTINCT ',' + days 
    FROM @tbl t
    WHERE t.id=t1.id and t._name=t1._name
    FOR xml path('')),1,1,'')
    , daysfrequency=STUFF((SELECT DISTINCT ',' + daysfrequency 
    FROM @tbl AS t
    WHERE t.id=t1.id and t._name=t1._name
    FOR XML PATH('')),1,1,'')
    , scheduletime=STUFF((SELECT DISTINCT ',' + scheduletime 
    FROM @tbl AS t
    WHERE t.id=t1.id and t._name=t1._name
    FOR XML PATH('')),1,1,'')
FROM @tbl AS t1 
GROUP BY id,_name;

Output

SQL #2

-- Method #2
-- pure XQuery
-- different sort order within columns in comparison with Method #1
DECLARE @separator CHAR(1) = ',';

SELECT id, _name
, (SELECT days FROM @tbl AS t
WHERE t.id=t1.id AND t._name=t1._name
FOR XML PATH(''), TYPE, ROOT('root')).query('
       for $i in /root/days
       return if ($i is (/root/days[last()])[1]) then string($i)
                    else concat($i, sql:variable("@separator"))
').value('.', 'NVARCHAR(MAX)') AS Days
, (SELECT DISTINCT daysfrequency FROM @tbl AS t
WHERE t.id=t1.id AND t._name=t1._name
FOR XML PATH(''), TYPE, ROOT('root')).query('
       for $i in /root/daysfrequency
       return if ($i is (/root/daysfrequency[last()])[1]) then string($i)
                    else concat($i, sql:variable("@separator"))
').value('.', 'NVARCHAR(MAX)') AS daysfrequency
, (SELECT scheduletime FROM @tbl AS t
WHERE t.id=t1.id AND t._name=t1._name
FOR XML PATH(''), TYPE, ROOT('root')).query('
       for $i in /root/scheduletime
       return if ($i is (/root/scheduletime[last()])[1]) then string($i)
                    else concat($i, sql:variable("@separator"))
').value('.', 'NVARCHAR(MAX)') AS scheduletime
FROM @tbl AS t1 
GROUP BY id,_name;

Output

If you have SQL Server Management Studio 2017 or later, you can very easily solve it using

STRING_AGG

Concatenates the values of string expressions and places separator values between them. The separator isn't added at the end of string.

Below is the SQL query

DECLARE @tbl TABLE (id INT, _name CHAR(1), days VARCHAR(10), daysfrequency VARCHAR(10), scheduletime VARCHAR(10));
INSERT INTO @tbl (id,_name,days,daysfrequency,scheduletime) VALUES
(1, 'a', 'Day4','Monthly','22:10'),
(1, 'a', 'Thu', 'Weekly', '07:30'),
(1, 'a', 'Fri', 'Daily',  '23:10'),
(2, 'b', 'Mon', 'Weekly', '20:00'),
(2, 'b', 'Tue', 'Weekly', '23:10'),
(2, 'b', 'Wed', 'Weekly', '18:10'),
(2, 'b', 'Thu', 'Weekly', '10:23'),
(2, 'b', 'Fri', 'Weekly', '1:23');


SELECT  t.id,
        t._name,
        STRING_AGG(t.days, ', ') WITHIN GROUP (ORDER BY t._name ASC) AS days,
        t1.daysfrequency,
        STRING_AGG(t.scheduletime, ', ') WITHIN GROUP (ORDER BY t._name ASC) AS scheduletime
FROM @tbl t INNER JOIN
(
    SELECT id, _name, STRING_AGG(daysfrequency, ', ') WITHIN GROUP (ORDER BY _name ASC) AS daysfrequency FROM
    (
        SELECT DISTINCT id, _name, daysfrequency FROM @tbl
    ) a
    GROUP BY id, _name
) t1
ON t.id = t1.id AND t._name = t1._name
GROUP BY t.id, t._name, t1.daysfrequency;