从第一个结果集创建第二个 SQL 视图

Question

我有一个返回以下示例结果集的存储过程。这是我在查询中获得的结果集。

Resource  |  ResourceGroup  |  ResourceType
----------|-----------------|----------------
 R1       |    RG1          |    RT1
 R1       |    RG2          |    RT1
 R2       |    RG2          |    RT2
 R3       |    RG3          |    RT2
 R4       |    RG1          |    RT2
----------|-----------------|---------------

我想操纵结果集以获得以下结果。

Resource     |  ResourceGroup  |  ResourceType
-------------|-----------------|----------------
R1,R2,R3,R4  |  RG1,RG2,RG3    |  RT1,RT2

Answer 1

将查询放入 CTE 中，您可以使用 对于 XML 路径：

declare @t1 table ([Resource] varchar(10), ResourceGroup varchar(10), ResourceType varchar(10));
insert into @t1 ([Resource], ResourceGroup, ResourceType)
values ('R1', 'RG1', 'RT1')
    , ('R1', 'RG2', 'RT1')
    , ('R2', 'RG2', 'RT2')
    , ('R3', 'RG3', 'RT2')
    , ('R4', 'RG1', 'RT2')
;
DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);
;with cte1 as (
    select * from @t1
)
select STUFF((SELECT DISTINCT ',' + [Resource] from @t1 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'') as [Resource]
    ,  STUFF((SELECT DISTINCT ',' + [ResourceGroup] from @t1 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'') as ResourceGroup
    ,  STUFF((SELECT DISTINCT ',' + [ResourceType] from @t1 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'') as ResourceType
;

Answer 2

您需要

使用 stuff() with xml 方法函数才能连接值

SELECT  DISTINCT
        STUFF(
               (SELECT +','+Resource FROM table GROUP BY Resource FOR XML PATH('')),1,1,''
            ) Resource,
        STUFF(
               (SELECT +','+ResourceGroup FROM table GROUP BY ResourceGroup FOR XML PATH('')),1,1,''
            ) ResourceGroup,
        STUFF(
               (SELECT +','+ResourceType FROM table GROUP BY ResourceType FOR XML PATH('')),1,1,''
            ) ResourceType