CODEWITHSUNDEEP
sqlsql-serverconcatenationdistinct-values
S.Hampton
S.Hampton

Reputation: 55

SQL query to CONCAT/list unique column data if rows have identical data in the other columns?

Thanks in advance. I'm basically trying to run a SQL query so that the results are 1:1:1:1:Many for StaffID:Name:Floor:Date:Shifts.

Here is my initial query and example results:

SELECT
    ST.STAFFNUM [StaffID],
    ST.FULLNAME [Name],
    ST.AREA [Floor],
    CONVERT(VARCHAR(10), TS.EVENTDATE, 103) [Date],
    LEFT(CONVERT(VARCHAR(10), TS.SHIFTSTART, 8), 5) [ShiftStart],
    LEFT(CONVERT(VARCHAR(10), TS.SHIFTEND, 8), 5) [ShiftEnd]
FROM
    TIMES TS
    LEFT JOIN STAFF ST ON TS.STAFFNUM = ST.STAFFNUM
WHERE
        TS.EVENTDATE BETWEEN '2021/01/01' AND '2021/01/01'
ORDER BY
    ST.AREA,
    ST.FULLNAME,
    TS.EVENTDATE,
    TS.SHIFTSTART
;

StaffID | Name    | Floor | Date       | ShiftStart | ShiftEnd
==============================================================
1000    | Andrew  | 1     | 01/01/2021 | 06:00      | 14:00
1000    | Andrew  | 1     | 01/01/2021 | 14:00      | 15:00
8654    | Belinda | 2     | 01/01/2021 | 06:00      | 14:00
9876    | Craig   | 3     | 01/01/2021 | 06:00      | 14:00

I then combined the ShiftStart and ShiftEnd columns with the following to get the further below results:

CONCAT(LEFT(CONVERT(VARCHAR(10), TS.SHIFTSTART, 8), 5),'-',LEFT(CONVERT(VARCHAR(10), TS.SHIFTEND, 8), 5)) [Shift]

StaffID | Name    | Floor | Date       | Shift
====================================================
1000    | Andrew  | 1     | 01/01/2021 | 06:00-14:00
1000    | Andrew  | 1     | 01/01/2021 | 14:00-15:00
8654    | Belinda | 2     | 01/01/2021 | 06:00-14:00
9876    | Craig   | 3     | 01/01/2021 | 06:00-14:00

What I can't figure out to do next though is to combine Andrew's shifts (and anyone else's multiple shifts on the same Date and Floor etc) like the below:

StaffID | Name    | Floor | Date       | Shifts
=================================================================
1000    | Andrew  | 1     | 01/01/2021 | 06:00-14:00, 14:00-15:00
8654    | Belinda | 2     | 01/01/2021 | 06:00-14:00
9876    | Craig   | 3     | 01/01/2021 | 06:00-14:00

NOTE: If someone is transferred to another Floor (occasional), I want to keep that Shift/s on separate rows unique to the Floor so the Floor data can be split up and emailed to that floor's manager. As long as all data of the rows are identical except for the Shift, I want to combine those rows and list the Shifts. Thanks again!

Upvotes: 0

Views: 889

Answers (1)

Anton Grig
Anton Grig

Reputation: 1719

As mentioned, you should use the String_Agg function as follows:

SELECT [StaffID], Max([Name]) As Name, [Floor], [Date], String_Agg([Shift], ',') WITHIN GROUP (ORDER BY [Shift]) As Shifts
FROM (
SELECT
    ST.STAFFNUM [StaffID],
    ST.FULLNAME [Name],
    ST.AREA [Floor],
    CONVERT(VARCHAR(10), TS.EVENTDATE, 103) [Date],
    CONCAT(LEFT(CONVERT(VARCHAR(10), TS.SHIFTSTART, 8), 5),'-',LEFT(CONVERT(VARCHAR(10), TS.SHIFTEND, 8), 5)) [Shift]
FROM
    TIMES TS
    LEFT JOIN STAFF ST ON TS.STAFFNUM = ST.STAFFNUM
WHERE
        TS.EVENTDATE BETWEEN '2021/01/01' AND '2021/01/01'
ORDER BY
    ST.AREA,
    ST.FULLNAME,
    TS.EVENTDATE,
    TS.SHIFTSTART) As T
Group by [StaffID], [Floor], [Date]

For Sql Server 2012, you can try "CTE" with "FOR XML PATH".

WITH CTE As
(SELECT
    ST.STAFFNUM [StaffID],
    ST.FULLNAME [Name],
    ST.AREA [Floor],
    CONVERT(VARCHAR(10), TS.EVENTDATE, 103) [Date],
    CONCAT(LEFT(CONVERT(VARCHAR(10), TS.SHIFTSTART, 8), 5),'-',LEFT(CONVERT(VARCHAR(10), TS.SHIFTEND, 8), 5)) [Shift]
FROM
    TIMES TS
    LEFT JOIN STAFF ST ON TS.STAFFNUM = ST.STAFFNUM
WHERE
        TS.EVENTDATE BETWEEN '2021/01/01' AND '2021/01/01'
ORDER BY
    ST.AREA,
    ST.FULLNAME,
    TS.EVENTDATE,
    TS.SHIFTSTART)
SELECT [StaffID], MAX([Name]) AS [Name], [Floor], [Date], 
       STUFF((SELECT ', ' + [Shift]  
              FROM CTE
              WHERE [StaffID] = T.[StaffID] AND [Floor] = T.[Floor] AND [Date] = T.[Date]
              FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)'),1,1,'') AS Shifts              
FROM CTE AS T
GROUP BY [StaffID], [Floor], [Date]

Upvotes: 1

Related Questions