CODEWITHSUNDEEP
sql-server
Deepu S
Deepu S

Reputation: 33

Create second SQL view from the first result set

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

Upvotes: 3

Views: 47

Answers (2)

Greg
Greg

Reputation: 4035

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
;

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You would require to use the stuff() with xml method function in order to concat the values

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

Upvotes: 0

Related Questions