如何在选择期间将多个行合并为一个行?

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:

我加入了一堆表格，然后将这些数据插入到一个表变量中。然后从表中选择这些记录。数据是这样的:

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.

从示例中可以看到，惟一的数据仅位于第7和第8列。在本例中，只有两行。但它可以是一个无限大的数。因此，我不想将一堆行发送到客户机，然后对数据进行排序，我想用SQL做它，只返回一行。

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:

最后，我有一行，Col7和Col8是这样的

Any ideas on how to accomplish this task?

对于如何完成这个任务有什么想法吗?

2 个解决方案

#1

You could try using FOR XML:

您可以尝试使用XML:

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

选择STUFF((SELECT'，' + Col7 FROM #test FOR XML PATH("))， 1, 1， ")作为Col7。

#2

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

假设您想要将整个表折叠成一行，并将数据丢弃在像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('')), 1, 1, ''),
  Col8 = STUFF((SELECT ',' + Col8 FROM @x FOR XML PATH('')), 1, 1, '');

Result:

结果:

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

If you need to keep the ID column values, then you need to provide better specs.

如果您需要保持ID列值，那么您需要提供更好的规范。