Skip to content
Advertisement

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

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

Is it possible to consolidate like ReferenceNumber rows into a single row, so:

I’ve tried:

which outputs:

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.

Produces:

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement