Combine field from two rows into single field

Question

question

dwarburton asked Sep 28, '16

Combine field from two rows into single field

Current row; Name Bldg CID John Doe 120 450 Next row; Name Bldg CID John Doe 120 500 Want to remove duplicate rows but add to the CID field of the other rows values Results; Name Bldg CID John Doe 120 450;500

t-sql mssql duplicate values

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2016-09-28T14:28:13Z

David Wimbush answered Sep 28, '16

Something like this should do the trick: select a.[Name] , a.Bldg , stuff (( select top 100 percent ';' + CID from b where b.[Name] = a.[Name] and b.Bldg = a.Bldg order by b.CID for xml path('') ), 1, 1, '') as CIDs from ( select distinct [Name] , Bldg from ) a order by a.[Name] , a.Bldg;

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question