Combine multiple rows into one by coalescing one column's value as CSV from two tables-CodePudding

I'll divide this into three parts:

What I have:

I have two tables Table1 and Table2.

Table1

ObjectName	Status
A	Active
C	Active

Table2

ParentObjectType	ChildObjectType
X	A
Y	C
Z	A
M	C

What I want:

I want to write a stored procedure that gives a result that looks something like this:

ObjectName	Status	ParentObjectName
A	Active	X, Z
C	Active	Y, M

What I have tried: I tried using the STUFF function and I'm getting a weird result.

Here's the query:

SELECT 
    ObjectName,
    Status,
    STUFF((SELECT '; '   table2.ParentObjectType 
           FROM table1 
           INNER JOIN table2 ON table1.[ObjectName] = table2.[ChildObjectName]
           FOR XML PATH('')), 1, 1, '') [ParentObjectName]
FROM 
    table1

Output

ObjectName	Status	ParentObjectName
A	Active	X, Z, Y, M
C	Active	X, Z, Y, M

Any help here is highly appreciated as I'm light handed on SQL and this is driving me nuts!

CodePudding user response：

Demo: Fiddle

You are missing WHERE condition in your Subquery for a parent table.

Also I assume this is a typo. In Table2 you have column ChildObjectType but in your link you are linking over ˛table2.[ChildObjectName]

SELECT 
    ObjectName,
    Status,
    STUFF((SELECT '; '   table2.ParentObjectType 
           FROM table1 
           INNER JOIN table2 ON table1.[ObjectName] = table2.[ChildObjectName]
           WHERE Table1.ObjectName = src.ObjectName
           FOR XML PATH('')), 1, 1, '') [ParentObjectName]
FROM 
    table1 src

Note: You can use STRING_AGG starting from SQL Server 2017 (14.x) and later

CodePudding user response：

This helped me realize I didn't have this saved in my snippets, thanks! Being careful thatFOR XML PATH will return XML Encoded text, so "&" becomes "&", see below for an example that shows you can add , TYPE to your FOR XML statement; This returns an xml datatype, that you can query the text out of with value('.',....

I personally tend to favor subqueries below the FROM, so this also shows an alternative style for joining the data, via a WHERE clause inside the APPLY refernce:

DECLARE @tt1 TABLE ( ObjectName VARCHAR(10), StatusValue VARCHAR(20) )
INSERT INTO @tt1 
          SELECT 'A','Active'
UNION ALL SELECT 'C','Active'
UNION ALL SELECT 'D&E','Active'


DECLARE @tt2 TABLE (  A VARCHAR(100), B VARCHAR(100) )
INSERT INTO @tt2 SELECT 'X','A'
INSERT INTO @tt2 SELECT 'Y','C'
INSERT INTO @tt2 SELECT 'Z','A'
INSERT INTO @tt2 SELECT 'M','C'
INSERT INTO @tt2 SELECT 'E&F','D&E' --sample "&" that should NOT render "&amp;"
INSERT INTO @tt2 SELECT '"G"','D&E'
INSERT INTO @tt2 SELECT 'F>G','C' --sample ">" that should NOT render "&gt;"


SELECT 
tt1.*,
f1.*
FROM
(SELECT ObjectName,StatusValue FROM @tt1) tt1
OUTER APPLY (SELECT 
    COALESCE(STUFF(
    (SELECT ','   CAST(tt2.A AS VARCHAR(10))
    FROM
    @tt2 tt2 WHERE tt2.B = tt1.ObjectName FOR XML PATH(''), TYPE ).value('.','nvarchar(max)'), 1,1,''),'') [csv1] ) f1

I'm assuming that you are on a SQL server version that does not have string aggregating functions?