sql-server - 从第一个结果集创建第二个 SQL View

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 中，您可以使用 FOR XML PATH:

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
;