Rolling Multiple Rows into a Single Row Based on Matching Data in SQL Server

Question

I have a table in SQL Server with the following layout and some sample data:

| 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

This isn't rolling or consolidating, this is aggregating, which requires GROUP BY. In SQL Server 2017 and later you can use STRING_AGG for this. In earlier versions FOR XML with an empty tag name is used to emulate the same effect. In SQL Server 2017 you can write SELECT RefNumber, STRING_AGG(',',Tag) from RefTagTable GROUP BY RefNumber — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Dec 9, 2020 at 16:36
The problem with this query is that it aggregates all rows each time, so the same output is repeated for each row. There's no correlation between the query that produces the aggregated string and the outer query — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Dec 9, 2020 at 16:38
Does this answer your question? sql server Concatenate Multiple Rows Using FOR XML PATH — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Dec 9, 2020 at 16:38

simon coleman · Accepted Answer · 2020-12-09 16:57:48Z

1

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

answered Dec 9, 2020 at 16:57

simon coleman

3732 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

simon coleman Over a year ago

This is actually the approach used in by the post that Panagiotis Kanavos references in the question comments, although it is not documented as a correlated subquery in that post.

Gregory A Beamer Over a year ago

And add a distinct and you scrunch things down.

Collectives™ on Stack Overflow

Rolling Multiple Rows into a Single Row Based on Matching Data in SQL Server

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related