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