SQL Server : multiple rows into one column and concatenate

Question

I have two tables and I want to concatenate and transpose into one - see below. Thanks for the help/

Table 1

ID   Name
-----------
1    Bob
2    Rich
3    Harry

Table 2

ID Opening Time   Closing Time   Day
------------------------------------------
1  8 AM           10 AM          Sunday
1  11 AM          12 PM          Sunday
1  8 AM           11 AM          Monday
1  8 AM           11 AM          Tuesday
2  9 AM           11 AM          Sunday
2  8 AM           11 AM          Monday
3  8 AM           11 AM          Sunday
3  8 AM           11 AM          Monday
3  8 AM           11 AM          Tuesday

I need this result

ID   Name   Sunday                     Monday          Tuesday
---------------------------------------------------------------------
1    Bob    8 AM - 10 AM 11 AM - 12PM  8 AM - 11 AM     8 AM - 11 AM
2    Rich   9 AM - 11 AM               8 AM - 11 AM 
3    Harry  8 AM - 11 AM               8 AM - 11 AM     8 AM - 11 AM

which version of SQL server you are using – Fahmi Nov 01 '20 at 16:58 — Fahmi, Nov 01 '20 at 16:58

score 1 · Answer 1 · answered Nov 01 '20 at 17:22

Here is full working example with using STRING_AGG:

DECLARE @DataSource01 TABLE
(
    [ID] INT
   ,[Name] VARCHAR(12)
);

DECLARE @DataSource02 TABLE
(
    [ID] INT
   ,[Opening Time] VARCHAR(8)
   ,[Closing Time] VARCHAR(8)
   ,[Day] VARCHAR(12)
);

INSERT INTO @DataSource01 ([ID], [Name])
VALUES (1, 'Bob')
      ,(2, 'Rich')
      ,(3, 'Harry');

INSERT INTO @DataSource02 ([ID], [Opening Time], [Closing Time], [Day])
VALUES (1, '8 AM', '10 AM', 'Sunday')
      ,(1, '11 AM', '12 PM', 'Sunday')
      ,(1, '8 AM', '11 AM', 'Monday')
      ,(1, '8 AM', '11 AM', 'Tuesday')
      ,(2, '9 AM', '11 AM', 'Sunday')
      ,(2, '8 AM', '11 AM', 'Monday')
      ,(3, '8 AM', '11 AM', 'Sunday')
      ,(3, '8 AM', '11 AM', 'Monday')
      ,(3, '8 AM', '11 AM', 'Tuesday');

SELECT *
FROM
(
    SELECT DS1.[ID]
          ,DS1.[Name]
          ,DS2.[Day]
          ,STRING_AGG(DS2.[Opening Time] + ' ' + DS2.[Closing Time], '')
    FROM @DataSource01 DS1
    INNER JOIN @DataSource02 DS2
        ON DS1.[ID] = DS2.[ID]
    GROUP BY DS1.[ID]
            ,DS1.[Name]
            ,DS2.[Day]
) DS ([ID], [Name], [Day], [Time])
PIVOT
(
    MAX([Time]) FOR [Day] IN ([Sunday], [Monday], [Tuesday])
) PVT
ORDER BY [ID];

If the days vary, you can use dynamic pivot or list all of them. Also, if you are using earlier then SQL Server 2017, you need to concatenate the vales with XML or SQL CLR string aggregator.

SQL Server : multiple rows into one column and concatenate

1 Answers1