0

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?

ernest
  • 1,633
  • 2
  • 30
  • 48
  • 1
    in oracle this would be a user defined aggregate function. please identify which DBMS you are using. – Randy Jul 22 '13 at 13:50
  • What if the data in those two columns contain commas? – Dan Bracuk Jul 22 '13 at 13:53
  • @DanBracuk They won't. The data for those columns is static and can only be inserted via a tool, which has a predefined list. – ernest Jul 22 '13 at 13:53
  • And what `ID`, `Col2` etc. values should be on this one magic row? – Aaron Bertrand Jul 22 '13 at 13:59
  • Have a look at this post http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string – Dev N00B Jul 22 '13 at 14:02
  • @AaronBertrand All the values are the same for every row except Col7 and Col8. Which is why every other row has "NonUniqueString" in it. So I'm only concatenating the unique data. The ID is irrelevant because the data isn't being saved. It's read only. – ernest Jul 22 '13 at 14:36
  • Except your screen shot of sample data has one row with `ID = 1`, and one row with `ID = 2`. Also col7 and col8 contain `UniqueString` but then you want the output to be `UniqueString1,UniqueString2` which doesn't match. So when you give specs that don't match the data, don't be too surprised that people question it. – Aaron Bertrand Jul 22 '13 at 14:38
  • @AaronBertrand You're just misunderstanding. The ID is a unique field. However, it's not important. It won't be used. I shouldn't have added that in the screenshot. The numbers added to UniqueString are just placeholders. They're also unimportant. I just need to take the Col7 and Col8 in each row and join them together in one row. All of the rest of the data I'm pulling is the same. Sorry for the confusion. – ernest Jul 22 '13 at 14:56
  • And did you try any of the answers that have been posted? Also when you post unclear, confusing and conflicting information, don't accuse others of "misunderstanding." We're trying to clarify your requirements so that we solve your problem - for free, mind you - correctly and efficiently. – Aaron Bertrand Jul 22 '13 at 14:58

2 Answers2

1

You could try using FOR XML:

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

mweber
  • 670
  • 7
  • 18
1

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;
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • Thanks, Aaron. That actually looks like what I need. I'll play around with that and see if it works. I still need the rest of the rows and not just Col7 and Col8. But hopefully I'll get it worked out. SQL isn't my best area, so I'm doing what I can. – ernest Jul 22 '13 at 14:59