CODEWITHSUNDEEP
sqlsql-serverstored-procedures
ernest
ernest

Reputation: 1724

How can I combine multiple rows into one during a Select?

I'm joining a bunch of tables and then inserting that data into a table variable. I then SELECT those records from the table. The data looks like this:

Sample SELECT

As you can see from the example, the unique data is only in column 7 and 8. In this example, there's only two rows. But it can be an infinite number. So instead of sending a bunch of rows to the client and then sorting out the data, I want to do it in SQL and only send back one row.

Since all of the data is the same, except for two columns, I wanted to concatenate the data and separate them by commas. This will make the client side operations much easier.

So in the end, I'll have one row and Col7 and Col8 will look like this:

Sample Data

Any ideas on how to accomplish this task?

Upvotes: 0

Views: 229

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

Assuming you want to collapse the entire table into a single row, and discard the data in columns like ID:

DECLARE @x TABLE(Col7 varchar(255), Col8 varchar(255));

INSERT @x SELECT 'foo','bar'
UNION ALL SELECT 'blat','splunge';

SELECT Col7 = STUFF((SELECT ',' + Col7 FROM @x FOR XML PATH(''),
              TYPE).value(N'./text()[1]', N'varchar(max)'), 1, 1, ''),
       Col8 = STUFF((SELECT ',' + Col8 FROM @x FOR XML PATH(''),
              TYPE).value(N'./text()[1]', N'varchar(max)'), 1, 1, '');

Result:

Col7       Col8
--------   -----------
foo,blat   bar,splunge

On SQL Server 2017+, it is much simpler:

SELECT Col7 = STRING_AGG(Col7, ','),
       Col8 = STRING_AGG(Col8, ',')
FROM @x;

Upvotes: 1

mweber
mweber

Reputation: 688

You could try using FOR XML:

SELECT STUFF((SELECT',' + Col7 FROM #test FOR XML PATH('')), 1, 1, '' ) as col7

Upvotes: 1

Related Questions