Create second SQL view from the first result set

Question

I have a stored procedure that returns the below sample result set. This is the result set I get in my query.

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

I would like to manipulate the result set to get the below result.

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

Answer 1

Put your query in the CTE, and you can use 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
;