SQL Server: Combine multiple rows into one row from a join table?

Question

I have an SQL query that looks like this.

SELECT ID, Data1, DataFromTable2
FROM Table1
LEFT JOIN Table2 on Table1.ID = Table2.ID
WHERE ID = 1

I'm trying to join the two tables where Table2 (which contains multiple rows with the same ID). And I want to the multiple rows into one row.

I found a post here that combines the multiple rows into one. SQL Server: combining multiple rows into one row

DECLARE @combinedString VARCHAR(MAX)
SELECT @combinedString = COALESCE(@combinedString + ', ', '') + stringvalue
FROM jira.customfieldValue
WHERE customfield = 12534
AND ISSUE = 19602

SELECT @combinedString as StringValue

But I'm having a hard time applying it to the above query.

Are you using MySQL or MS SQL Server? – jarlh Oct 14 '19 at 09:49 — jarlh, Oct 14 '19 at 09:49
SQL server. my bad, forgot to mention in the post. – bigbryan Oct 14 '19 at 09:51 — bigbryan, Oct 14 '19 at 09:51

Tim Biegeleisen · Accepted Answer · 2019-10-14T10:35:54.703

3

Use a GROUP BY query with STRING_AGG:

SELECT
    t1.ID,
    t1.Data1,
    STRING_AGG(t2.DataFromTable2, ',') AS data
FROM Table1 t1
LEFT JOIN Table2 t2
    ON t1.ID = t2.ID
GROUP BY
    t1.ID,
    t1.Data1
WHERE
    t1.ID = 1;

I am assuming you are using SQL Server 2017 or later. For earlier versions of SQL Server:

SELECT
    t1.ID,
    t1.Data1,
    data = STUFF((
      SELECT ',' + t2.DataFromTable2
      FROM Table2 t2
      WHERE t2.ID = t1.ID
      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM Table1 t1;

edited Oct 14 '19 at 10:35

answered Oct 14 '19 at 09:52

Tim Biegeleisen

502,043
27
286
360

I'm using SQL Server 2017 and 2018 but I'm getting an error Msg 195, Level 15, State 10, Line 566 'STRING_AGG' is not a recognized built-in function name. – bigbryan Oct 14 '19 at 10:10
1

Then maybe your version does not support it. – Tim Biegeleisen Oct 14 '19 at 10:11
i see. are there other ways to do it aside from your answer? – bigbryan Oct 14 '19 at 10:19
1

@bigbryan Yes, you may use `STUFF`, but you need a big ugly query in this case. Strange that `STRING_AGG` does not work for you. – Tim Biegeleisen Oct 14 '19 at 10:36
1

@bigbryan . . . Check your compatibility level: https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver15. – Gordon Linoff Oct 14 '19 at 10:41

SQL Server: Combine multiple rows into one row from a join table?

1 Answers1

Related