CODEWITHSUNDEEP
sql-serversql-server-2014
c.abb
c.abb

Reputation: 21

Multiple records associated with one id

I have a table that looks like this...

ID   | Cat Type |   LOB
ID_1 |  Cat_1   |   lob_1
ID_1 |  Cat_1   |   lob_2

That i want to look like this...

ID  | Cat Type | LOB
ID_1  Cat_1      LOB_1, LOB_2

How do I approach this in SQL?

Upvotes: 2

Views: 50

Answers (2)

gofr1
gofr1

Reputation: 15977

Or without outer apply:

DECLARE @Table1 TABLE (
    ID nvarchar(4),
    CatType nvarchar(5),
    LOB nvarchar(5)
)

INSERT INTO @Table1 VALUES 
('ID_1', 'Cat_1', 'lob_1'),
('ID_1', 'Cat_1', 'lob_2')

SELECT DISTINCT 
        ID,
        CatType,
        STUFF((SELECT ', ' + LOB  FROM @Table1 t WHERE t.ID = ID FOR XML PATH('')), 1, 2, '') as LOB
FROM @Table1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269843

This is a basic string aggregation query (which isn't so basic in SQL Server). Here is one method that uses outer apply:

select id, cat_type,
       stuff(tt.lob, 1, 2, '') as lobs
from (select distinct id, cat_type
      from t
     ) t outer apply
     (select ', ' + LOB
      from t t2
      where t2.id = t.id and t2.cat_type = t.cat_type
      for xml path ('')
     ) tt(lob);

Upvotes: 1

Related Questions