SQL to combine multiple rows into a single row

Question

I am currently writing a SQL script - takes a business term, and all related synonyms. What it does is creates multiple rows (because there are multiple synonyms (can have other columns that could be multiple values as well.

What I am trying to do is to create a single row for every business term, and concatenate values (, delimited) so that I get one line item for each business term only.

Currently my SQL script is:

SELECT dbo.TblBusinessTerm.BusinessTerm, dbo.TblBusinessTerm.BusinessTermLongDesc, 
       dbo.TblBusinessTerm.DomainCatID, dbo.TblSystem.SystemName, 
       dbo.TblDomainCat.DataSteward, dbo.TblDomainCat.DomainCatName, 
       dbo.TblField.GoldenSource, dbo.TblField.GTS_table, 
       dbo.TblTableOwner.TableOwnerName, dbo.TblBusinessSynonym.Synonym 
FROM   dbo.TblTableOwner INNER JOIN
       dbo.TblBusinessTerm INNER JOIN
       dbo.TblBusinessSynonym ON dbo.TblBusinessTerm.BusinessTermID = dbo.TblBusinessSynonym.BusinessTermID INNER JOIN
       dbo.TblField ON dbo.TblBusinessTerm.BusinessTermID = dbo.TblField.BusinessTermID INNER JOIN
       dbo.TblSystem INNER JOIN
       dbo.TblTable ON dbo.TblSystem.SystemID = dbo.TblTable.SystemID ON dbo.TblField.TableID = dbo.TblTable.TableID INNER JOIN
       dbo.TblDomainCat ON dbo.TblBusinessTerm.DomainCatID = dbo.TblDomainCat.DomainCatID ON dbo.TblTableOwner.TableOwnerID = dbo.TblDomainCat.DataSteward

Is there an easy way to do this that takes performance into consideration - am new to SQL.

Thank you

I have managed to create a with statement that now concatenates my rows:

With syn as (
    select [BusinessTermID],
           syns = STUFF((SELECT  ', ' + dbo.TblBusinessSynonym.Synonym
                         FROM   dbo.TblBusinessSynonym
                         WHERE  [BusinessTermID] = x.[BusinessTermID]
                             AND    dbo.TblBusinessSynonym.Synonym <> ''
                         FOR XML PATH ('')),1,2,'')
    FROM dbo.TblBusinessSynonym AS x
    GROUP BY [BusinessTermID]
)
select * from syn

But now how can I use it in the above query where everything links?

Would want to replace dbo.TblBusinessSynonym.Synonym with the results from syn

Any SQL 2014 developers that can assist?

Possible duplicate of https://stackoverflow.com/questions/15154644/group-by-to-combine-concat-a-column — schlonzo, Apr 17 '18 at 05:30
possibly you need to use `CONCAT_WS` for delimited or `CONCAT` for regular values. — iSR5, Apr 17 '18 at 06:26

score 1 · Answer 1 · answered Apr 17 '18 at 08:20

1

Please use STRING_AGG function. It combines record items in field ans set them in one record separated with specified delimiter.

Details are here: https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017

answered Apr 17 '18 at 08:20

Alex Yena

67
2

STRING_AGG is not recognized. – Jext Apr 17 '18 at 09:03
This function isn't supported before MSSQL 2016. – Michał Turczyn Apr 17 '18 at 11:03
It was something like this here https://stackoverflow.com/questions/15477743/listagg-in-sqlserver – Alex Yena Apr 18 '18 at 15:03

jigga · Accepted Answer · 2018-04-18T06:16:26.750

Write your with statement at the very top, without the select. Then write your upper query as it is and change

INNER JOIN dbo.TblBusinessSynonym ON dbo.TblBusinessTerm.BusinessTermID = dbo.TblBusinessSynonym.BusinessTermID

to

INNER JOIN syn ON syn.BusinessTermID = dbo.TblBusinessTerm.BusinessTermID

That's it

With syn as (
    select [BusinessTermID],
           syns = STUFF((SELECT  ', ' + dbo.TblBusinessSynonym.Synonym
                         FROM   dbo.TblBusinessSynonym
                         WHERE  [BusinessTermID] = x.[BusinessTermID]
                             AND    dbo.TblBusinessSynonym.Synonym <> ''
                         FOR XML PATH ('')),1,2,'')
    FROM dbo.TblBusinessSynonym AS x
    GROUP BY [BusinessTermID]
)

SELECT dbo.TblBusinessTerm.BusinessTerm, 
       dbo.TblBusinessTerm.BusinessTermLongDesc, 
       dbo.TblBusinessTerm.DomainCatID, dbo.TblSystem.SystemName, 
       dbo.TblDomainCat.DataSteward, dbo.TblDomainCat.DomainCatName, 
       dbo.TblField.GoldenSource, dbo.TblField.GTS_table, 
       dbo.TblTableOwner.TableOwnerName, syn.syns 
FROM   dbo.TblTableOwner INNER JOIN
       dbo.TblBusinessTerm INNER JOIN
       syn ON dbo.TblBusinessTerm.BusinessTermID = syn.BusinessTermID INNER JOIN
       dbo.TblField ON dbo.TblBusinessTerm.BusinessTermID = dbo.TblField.BusinessTermID INNER JOIN
       dbo.TblSystem INNER JOIN
       dbo.TblTable ON dbo.TblSystem.SystemID = dbo.TblTable.SystemID ON   dbo.TblField.TableID = dbo.TblTable.TableID INNER JOIN
       dbo.TblDomainCat ON dbo.TblBusinessTerm.DomainCatID = dbo.TblDomainCat.DomainCatID ON dbo.TblTableOwner.TableOwnerID = dbo.TblDomainCat.DataSteward

Managed to get script to run - thank you - but now the SYNS doesn't display as a column header at all. Have reposted ammeded code — Jext, Apr 18 '18 at 05:25
I've added the full query... a LEFT JOIN in **syn** is maybe the better way in your query — jigga, Apr 18 '18 at 05:57
Thank you. Still doesnt like the like `dbo.TblTableOwner.TableOwnerName, syn.syns ` has an issue syn.sys - says multi part identifier could not be bound — Jext, Apr 18 '18 at 06:06
No type, has issue on `dbo.syn ON dbo.TblBusinessTerm.BusinessTermID = syn.BusinessTermID ` as well - says the dbo.syn - invalid object name. It does not seem to be picking up the SYN in the with statement and carrying it through to rest of query — Jext, Apr 18 '18 at 06:15

Michał Turczyn · Answer 3 · 2018-04-17T11:03:47.950

0

Your query is complicated, so I will just post here sample data and how to deal with it in a manner you want. The operation is string aggregation with concatenation, in latest version there's string_agg function, that does the work for us. But, as you can't use this function, here's workaround:

select * into #tt
from (values (1, '1'),(1, '2'),(2, '1'),(2, '2')) A(id, someStr) 

select id, (select someStr + ',' from #tt where id = [t].id for xml path('')) [grouped]
from #tt [t] group by id

Above query groups by Id and concaenates all corresponding rows in someStr column.

edited Apr 17 '18 at 11:03

answered Apr 17 '18 at 10:52

Michał Turczyn

32,028
14
47
69

agreed - query which is simple I have over complicated, but am not able to associated what you have to what I have in respect of table joins etc - have only started in SQL server, so still trying to find my way around. – Jext Apr 18 '18 at 05:00

SQL to combine multiple rows into a single row

3 Answers3