CODEWITHSUNDEEP
sqlsql-servert-sql
baller
baller

Reputation: 1

SQL merging columns where only one column differ

I use SQL Server 2017 and have a simple query with joins that looks like this.

SELECT DISTINCT
    [p].[MainTitle] AS Title,
    [pe].[Name] AS JobName,
    [ea].[Label] AS Stat
FROM
    [Project] AS [P]
INNER JOIN 
    [ProjectExtended] AS [pe] ON [p].[ID] = [pe].[ProjectID]
INNER JOIN 
    [ExtAttributes] AS [ea] ON [pe].[AttributeID] = [ea].[ID]
      ...
WHERE 
    pe.date BETWEEN @start AND @end
    AND [ea].Label IN ('A', 'B', 'C')

The result right know looks like this (JobName is different for every Title but always the same for the same Title)

+-----------+----------+--------------+
| Title     | JobName  | Stat         | 
+-----------+----------+--------------+
|      BAMB | asdf     |  C           | 
|      BIRT | fdsa     |  B           | 
|      BIRT | fdsa     |  A           |
|      BOND | lofe     |  B           |
+-----------+----------+--------------+

But I want it to show like this.

+-----------+----------+--------------+
| Title     | JobName  | Stat         | 
+-----------+----------+--------------+
|      BAMB | asdf     |  C           |
|      BIRT | fdsa     |  B, A        |
|      BOND | lofe     |  B           |
+-----------+----------+--------------+

I tried with different versions of CONCAT, STRING_AGG functions that you suggested in different threads, but no success. I only got to the point where it joins all the stats in last column - result was like A, B, C in every row.

Upvotes: 0

Views: 75

Answers (1)

Rui Costa
Rui Costa

Reputation: 437

This should help:

SELECT 
    [t].[Title] AS Title,
    [t].[JobName] AS JobName,
    STRING_AGG([t].[Stat], ',') AS Stat
FROM (
    SELECT DISTINCT
        [p].[MainTitle] AS Title,
        [pe].[Name] AS JobName,
        [ea].[Label] AS Stat
    FROM
        [Project] AS [P]
    INNER JOIN 
        [ProjectExtended] AS [pe] ON [p].[ID] = [pe].[ProjectID]
    FULL OUTER JOIN 
        [ExtAttributes] AS [ea] ON [pe].[AttributeID] = [ea].[ID]
        ...
    WHERE 
        pe.date BETWEEN @start AND @end
        AND [ea].Label IN ('A', 'B', 'C', NULL)
) t
GROUP BY 
[t].[Title]
, [t].[JobName]

It says version 15 but hopefully is available on yours also https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15

Upvotes: 2

Related Questions