SQL Row concatenation with XML PATH and STUFF giving aggregate sql error

Question

I am trying to query two tables and get results like the following:

Section Names
shoes AccountName1, AccountName2, AccountName3
books AccountName1

The tables are:

CREATE TABLE dbo.TableA(ID INT, Section varchar(64), AccountId varchar(64));
INSERT dbo.TableA(ID, Section, AccountId) VALUES
(1 ,'shoes','A1'),
(2 ,'shoes','A2'),
(3 ,'shoes','A3'),
(4 ,'books','A1');
CREATE TABLE dbo.TableB(AccountId varchar(20), Name varchar(64));
INSERT dbo.TableB(AccountId, Name) VALUES
('A1','AccountName1'),
('A2','AccountName2'),
('A3','AccountNAme3');

I saw a few questions answered saying to use "XML PATH" and "STUFF" to query the data to get the results I am looking for, but I think there is something missing. I have tried the below query and get the error message:

Column 'a.AccountId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I don't have it in the SELECT clause of either query, but I assume the error is because AccountId is not unique in TableA.

Here is the query I am currently trying to get working correctly.

SELECT section, names= STUFF((
 SELECT ', ' + Name FROM TableB as b 
WHERE AccountId = b.AccountId
FOR XML PATH('')), 1, 1, '')
FROM TableA AS a
GROUP BY a.section

Answer 1

Sorry, I missed a step in the relationship. Try this version (though Martin's will work as well):

SELECT DISTINCT o.section, names= STUFF((
 SELECT ', ' + b.Name 
 FROM dbo.TableA AS a
 INNER JOIN dbo.TableB AS b
 ON a.AccountId = b.AccountId
 WHERE a.Section = o.Section
 FOR XML PATH, TYPE).value(N'.[1]', N'varchar(max)'), 1, 2, '')
FROM dbo.TableA AS o;

An approach that is at least as good, but sometimes better, is switching from DISTINCT to GROUP BY:

SELECT o.section, names= STUFF((
 SELECT ', ' + b.Name 
 FROM dbo.TableA AS a
 INNER JOIN dbo.TableB AS b
 ON a.AccountId = b.AccountId
 WHERE a.Section = o.Section
 FOR XML PATH, TYPE).value(N'.[1]', N'varchar(max)'), 1, 2, '')
FROM dbo.TableA AS o
GROUP BY o.section;

At a high level, the reason DISTINCT applies to the entire column list. Therefore for any duplicates it has to perform the aggregate work for every duplicate before applying DISTINCT. If you use GROUP BY then it can potentially remove duplicates before doing any of the aggregation work. This behavior can vary by plan depending on a variety of factors including indexes, plan strategy, etc. And a direct switch to GROUP BY may not be possible in all cases.

In any case, I ran both of these variations in SentryOne Plan Explorer. The plans are different in a few minor, uninteresting ways, but the I/O involved with the underlying worktable is telling. Here is DISTINCT:

enter image description here

And here is GROUP BY:

enter image description here

When I made the tables larger (14,000+ rows mapping to 24 potential values), this difference is more pronounced. DISTINCT:

enter image description here

GROUP BY:

enter image description here

In SQL Server 2017, you can use STRING_AGG:

SELECT a.section, STRING_AGG(b.Name, ', ')
 FROM dbo.TableA AS a
 INNER JOIN dbo.TableB AS b
 ON a.AccountId = b.AccountId
 WHERE a.Section = a.Section
 GROUP BY a.section;

The I/O here is almost nothing:

enter image description here

---

But, if you're not on SQL Server 2017 (or Azure SQL Database), and can't use STRING_AGG, I have to give credit where credit is due... Paul White's answer below has very little I/O and kicks the pants off of both of the FOR XML PATH solutions above.

enter image description here

---

Other enhancements from these posts:

• Grouped Concatenation in SQL Server
• Grouped Concatenation : Ordering and Removing Duplicates
• Comparing string splitting / concatenation methods

Also see:

• Bad habits to kick : avoiding the schema prefix

Answer 2

I thought I would try a solution using XML.

SEDE Demo

Tables

DECLARE @TableA AS table
(
 ID integer PRIMARY KEY,
 Section varchar(10) NOT NULL,
 AccountID char(2) NOT NULL
);
DECLARE @TableB AS table
(
 AccountID char(2) PRIMARY KEY,
 Name varchar(20) NOT NULL
);

Data

INSERT @TableA
 (ID, Section, AccountID)
VALUES
 (1, 'shoes', 'A1'),
 (2, 'shoes', 'A2'),
 (3, 'shoes', 'A3'),
 (4, 'books', 'A1');
INSERT @TableB
 (AccountID, Name)
VALUES
 ('A1', 'AccountName1'),
 ('A2', 'AccountName2'),
 ('A3', 'AccountName3');

Join and convert to XML

DECLARE @x xml =
(
 SELECT
 TA.Section,
 CA.Name
 FROM @TableA AS TA
 JOIN @TableB AS TB
 ON TB.AccountID = TA.AccountID
 CROSS APPLY
 (
 VALUES(',' + TB.Name)
 ) AS CA (Name)
 ORDER BY TA.Section
 FOR XML AUTO, TYPE, ELEMENTS, ROOT ('Root')
);

XML creation query

The XML in the variable looks like this:

<Root>
 <TA>
 <Section>shoes</Section>
 <CA>
 <Name>,AccountName1</Name>
 </CA>
 <CA>
 <Name>,AccountName2</Name>
 </CA>
 <CA>
 <Name>,AccountName3</Name>
 </CA>
 </TA>
 <TA>
 <Section>books</Section>
 <CA>
 <Name>,AccountName1</Name>
 </CA>
 </TA>
</Root>

Query

The final query shreds the XML into sections, and concatenates the names in each:

SELECT
 Section = 
 N.n.value('(./Section/text())[1]', 'varchar(10)'),
 Names = 
 STUFF
 (
 -- Consecutive text nodes collapse
 N.n.query('./CA/Name/text()')
 .value('./text()[1]', 'varchar(8000)'), 
 1, 1, ''
 )
-- Shred per section
FROM @x.nodes('Root/TA') AS N (n);

Result

Output

Execution plan

Execution plan