带有 Group By 子句的 SQL 逗号分隔行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7448734/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL comma-separated row with Group By clause
提问by mameesh
I have the following query:
我有以下查询：
SELECT
Account,
Unit,
SUM(state_fee),
Code
FROM tblMta
WHERE MTA.Id = '123'
GROUP BY Account,Unit
This of course throws an exception because the Code is not in the group byclause. Each state_fee has a code. How do I get this code to display in 1 record (1 code per state_fee which is multiple state_fee per unit) as a comma-separated list? I looked into different solutions on here but I couldn't find any that worked with a group by.
这当然会引发异常，因为代码不在group by子句中。每个 state_fee 都有一个代码。如何让此代码以逗号分隔列表的形式显示在 1 个记录中（每个 state_fee 1 个代码，即每个单位的多个 state_fee）？我在这里查看了不同的解决方案，但找不到任何适用于group by.
回答by Adriano Carneiro
You want to use FOR XML PATHconstruct:
你想使用FOR XML PATH构造：
SELECT ACCOUNT,
unit,
SUM(state_fee),
Stuff((SELECT ', ' + code
FROM tblmta t2
WHERE t2.ACCOUNT = t1.ACCOUNT
AND t2.unit = t1.unit
AND t2.id = '123'
FOR XML PATH('')), 1, 2, '') [Codes]
FROM tblmta t1
WHERE t1.id = '123'
GROUP BY ACCOUNT,
unit
See other examples here:
在此处查看其他示例：
回答by BlueMonkMN
There is no built-in aggregate function to concatenate, but this article discusses several alternative solutions, including a user-defined concatenate aggregate function:
没有内置聚合函数来连接，但本文讨论了几种替代解决方案，包括用户定义的连接聚合函数：
https://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/
https://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/
回答by Darky711
This will show you the table, index name, index type, indexed columns, and included columns:
这将显示表、索引名称、索引类型、索引列和包含的列：
with [indexes] (table_name, index_name, column_name, index_id, key_ordinal, object_id, type_desc)
as(
SELECT distinct
T.[name] AS [table_name], I.[name] AS [index_name],
AC.[name] AS [column_name],
I.[index_id], IC.[key_ordinal], T.[object_id], i.type_desc
FROM sys.[tables] AS T
INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]
INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id] and IC.index_id=I.index_id
LEFT OUTER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id] AND IC.[column_id] = AC.[column_id]
WHERE T.[is_ms_shipped] = 0 AND I.[type_desc] <> 'HEAP'
)
select
distinct
db_name() as dbname,
type_desc,
table_name,
index_name,
column_name,
STUFF((
select ', ' + column_name
from [indexes] t2
where t1.table_name=t2.table_name and t1.[index_name]=t2.[index_name] and t2.[key_ordinal] = 0
for xml path('')), 1, 2, '') inc_cols
from [indexes] t1
where t1.[key_ordinal] = 1
GROUP BY table_name, index_name, type_desc, column_name
