Concat column values based on other columns groups - each column from different table

Question

I am trying to write a query in SQL that will bring me a table with columns from different tables and in one column I want to concat the values based on groups from the other columns. my data is like

And I want this outcome

The 3 columns are from different tables and I have only read access to the database.

Does this answer your question? [Simulating group\_concat MySQL function in Microsoft SQL Server 2005?](https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) — Charlieface, Jan 23 '22 at 02:32
Which version of Sql Server? The answer will be very different from Sql Server 2017 and later vs older versions. — Joel Coehoorn, Jan 31 '22 at 15:15

score 0 · Answer 1 · answered Jan 21 '22 at 19:35

This is a pretty straightforward request using STRING_AGG (requires SQL Server 2017 (14.x)+).

Run the below in SSMS.

DECLARE @data table ( Program varchar(20), Subprogram varchar(20), Region varchar(20) );
INSERT INTO @data VALUES
    ( 'program 1', 'subprogram 1', 'Region 1' ),
    ( 'program 1', 'subprogram 1', 'Region 2' ),
    ( 'program 1', 'subprogram 1', 'Region 3' ),
    ( 'program 1', 'subprogram 1', 'Region 4' ),
    ( 'program 2', 'subprogram 2', 'Region 5' );

SELECT
    Program, Subprogram, STRING_AGG ( Region, ',' ) AS Region
FROM @data
GROUP BY
    Program, Subprogram
ORDER BY
    Program, Subprogram, Region;

Returns

+-----------+--------------+-------------------------------------+
|  Program  |  Subprogram  |               Region                |
+-----------+--------------+-------------------------------------+
| program 1 | subprogram 1 | Region 1,Region 2,Region 3,Region 4 |
| program 2 | subprogram 2 | Region 5                            |
+-----------+--------------+-------------------------------------+

Concat column values based on other columns groups - each column from different table

1 Answers1