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