I have a data set where all the other information in the row is identical except for one column "Issues" as there can be multiple issues but each issue gets put in to a new row. I'd like to join those rows if everything else in that row is identical. I've found solutions to join different columns together but can't quite get it for the same column being joined if everything else is the same. The column is a character string.
Example Data:
| DateTime | StaffID | ClientID | Enquiry | Issue |
|---|---|---|---|---|
| 25/08/2022 15:55 | 79 | 001 | Relationship | Behaviour |
| 25/08/2022 15:55 | 79 | 001 | Relationship | Social |
| 25/08/2022 15:55 | 79 | 001 | Relationship | Child |
| 25/08/2022 15:55 | 79 | 001 | Relationship | Anxiety |
| 25/08/2022 10:15 | 450 | 002 | Sleep | Feeding |
| 25/08/2022 10:15 | 450 | 002 | Sleep | Settle |
Outcome I'd like to create: I want to join the 'Issue' column where the other columns are the same
| DateTime | StaffID | ClientID | Enquiry | Issue |
|---|---|---|---|---|
| 25/08/2022 15:55 | 79 | 001 | Relationship | Behaviour, Social, Child, Anxiety |
| 25/08/2022 10:15 | 450 | 002 | Sleep | Feeding, Settle |
CodePudding user response：
Try this out, but do note that, STRING_AGG() applies to SQL Server 2017 (14.x) and later
SELECT DateTime, StaffID, ClientID, Enquiry, STRING_AGG(Issue, ', ') AS Issues
FROM TableName
GROUP BY DateTime, StaffID, ClientID, Enquiry
Please read documentation for STRING_AGG
STRING_AGG ( expression, separator )
Since you want to join two or more values of the column issue, you just need to change expression to issue, and your separator, for instance, comma is my separator