admin 管理员组文章数量: 1184232
2024年4月17日发(作者：db2数据库引擎)
SQL Server Example to Rolling up Multiple Rows into a Single Row
Example 1
SELECT
_NAME,
(SELECT '; ' + _NAME
FROM USRS US
WHERE _ID = _ID
FOR XML PATH('')) [SECTORS/USERS]
FROM SALES_SECTORS SS
GROUP BY _ID, _NAME
ORDER BY 1
SELECT
_NAME,
STUFF((SELECT '; ' + _NAME
FROM USRS US
WHERE _ID = _ID
FOR XML PATH('')), 1, 1, '') [SECTORS/USERS]
FROM SALES_SECTORS SS
GROUP BY _ID, _NAME
ORDER BY 1
SELECT
_NAME,
STUFF((SELECT '; ' + _NAME
FROM USRS US
WHERE _ID = _ID
ORDER BY USR_NAME
FOR XML PATH('')), 1, 1, '') [SECTORS/USERS]
FROM SALES_SECTORS SS
GROUP BY _ID, _NAME
ORDER BY 1
Example 2
SELECT
_NAME + ': ' +
STUFF((SELECT '; ' + _NAME
FROM USRS US
WHERE _ID = _ID
FOR XML PATH('')), 1, 1, '') [SECTORS/USERS]
FROM SALES_SECTORS SS
GROUP BY _ID, _NAME
ORDER BY 1
Example 3
SELECT
_ID,
_NAME,
STUFF((SELECT '; ' + _NAME + ' (' +
STUFF((SELECT ',' + _NAME
FROM USR_ADV_CAMPAIGN UAC
INNER JOIN USRS US
ON _ID = _ID
WHERE _ID = _ID
FOR XML PATH('')), 1, 1, '') + ')'
FROM ADV_CAMPAIGN AC
INNER JOIN SEC_ADV_CAMPAIGN SAC
ON _ID = _ID AND _ID = _ID
ORDER BY _NAME
FOR XML PATH('')), 1, 1, '') [CAMPAIGNS/USERS PER SECTOR]
FROM SALES_SECTORS SS
GROUP BY
_ID,
_NAME
Example Rolling Up Index Columns into One Row
SELECT
SCHEMA_NAME(_id) AS SchemaName,
as TableName,
as IndexName,
_id,
_desc,
STUFF((SELECT ', ' + name
from _columns a inner join _columns b on _id = _id and _id = _id and _id = _id and _id = _id and is_included_column = 0
order by _ordinal
FOR XML PATH('')), 1, 2, '') IndexColumns,
STUFF((SELECT ', ' + name
from _columns a inner join _columns b on _id = _id and _id = _id and _id = _id and _id = _id and is_included_column = 1
FOR XML PATH('')), 1, 2, '') IncludedColumns
FROM s SS INNER JOIN S ss2 ON _ID = _ID
WHERE = 'U'
ORDER BY 1, 2, 3
版权声明：本文标题：将多行汇总为SQLServer数据的一行和一列 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.roclinux.cn/p/1713354963a630572.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论