CODEWITHSUNDEEP
sql-server-2008reporting-services
tnktnk
tnktnk

Reputation: 512

COALESCE to get comma separated values on one row

Trying to get a comma separated list of parameters for each report listed in the catalog table of the SQL Reporting Services database: 

WITH cte AS (
    SELECT
    [Path], [Name], CreationDate, ModifiedDate,    
    CAST(parameter AS XML) AS parameter
    FROM     [dbo].[Catalog] AS c
)
SELECT  [Path], [Name], CreationDate, ModifiedDate, COALESCE(p.nodes('./Name')) 
FROM    cte AS c
    LEFT JOIN c.Parameter.nodes('/Parameters/Parameter') AS T ( p )
ORDER BY c.[Path]

In BOL it says COALESCE is used to return the first non null value but I've read it can be used to comma separate a returned table of values.

Upvotes: 0

Views: 1642

Answers (1)

Ben Thul
Ben Thul

Reputation: 32697

I don't know of any way to use COALESCE to generate CSV. I did, however, use a standard trick with XML PATH queries:

WITH cte AS (
    SELECT  [Path] ,
            [Name] ,
            CreationDate ,
            ModifiedDate ,
            CAST(parameter AS XML) AS parameter
    FROM    [dbo].[Catalog] AS c
), 
cte2 AS (
SELECT  [Path] ,
        p.value('(./Name)[1]', 'nvarchar(50)') AS [Parameter]
FROM    [cte] AS c
CROSS APPLY c.parameter.nodes('/Parameters/Parameter') AS T ( p )
)

SELECT [Path], 
    STUFF((
        SELECT ',' + [Parameter] 
        from cte2 c2
        WHERE c.[Path] = c2.[Path]    
        FOR XML PATH('') 
    ), 1, 1, '')
from [dbo].[Catalog] AS c

Essentially, by throwing "FOR XML PATH('')" on the end of the query, you say "take all of the rows returned and concatenate them together with an empty string as the surrounding tag". While this gets you where you need to go, play around with it to see how it behaves if you pass in something besides an empty string.

Upvotes: 1

Related Questions