I have a table in SQL Server with the following layout and some sample data:
x
| ReferenceNumber | TagID |
+-----------------+-------+
| 114942 | 1 |
| 114942 | 2 |
| 114942 | 3 |
| 114942 | 4 |
| 123456 | 10 |
Is it possible to consolidate like ReferenceNumber rows into a single row, so:
| ReferenceNumber | TagID |
+-----------------+---------+
| 114942 | 1,2,3,4 |
| 123456 | 10 |
I’ve tried:
select
ReferenceNumber,
stuff((select '; ' + TagID from RefTagTable
for xml path('')), 1, 1,'')[Tags]
from
RefTagTable
order by
ReferenceNumber
which outputs:
| ReferenceNumber | Tags |
+-----------------+------------+
| 114942 | 1,2,3,4,10 |
| 123456 | 1,2,3,4,10 |
Any help would be greatly appreciated. Thanks
Advertisement
Answer
Assuming this is for small (ish) amount of data, you can correlate the subquery. It nests (so the query can be thought of as running once per row in the outer query. The optimiser normally optimises better than that but depends a bit on volumes as to whether it works for you better than other approaches.
DROP TABLE #tags
GO
CREATE TABLE #tags (ReferenceNumber varchar(10), TagId varchar(20))
GO
INSERT INTO #tags (ReferenceNumber , TagId )
SELECT 114942, 1
UNION SELECT 114942, 2
UNION SELECT 114942, 3
UNION SELECT 114942, 4
UNION SELECT 123456, 1
GO
SELECT ReferenceNumber,
stuff((select '; ' + TagID from #Tags AS CorrTagTable WHERE CorrTagTable.ReferenceNumber = Reftagtable.ReferenceNumber
for xml path('')), 1, 1,'')[Tags]
from #tags AS RefTagTable
order by ReferenceNumber
GO
Produces:
ReferenceNumber Tags
114942 1; 2; 3; 4
114942 1; 2; 3; 4
114942 1; 2; 3; 4
114942 1; 2; 3; 4
123456 1