sql-CodePudding

 
The CREATE TABLE # C (id VARCHAR (20), the name VARCHAR (200)) 
INSERT INTO # C 
SELECT 'c1', 'A001' UNION ALL 
SELECT 'c2', 'A002' UNION ALL 
SELECT 'c3', 'A003' UNION ALL 
SELECT 'c4', 'A004' 

The CREATE TABLE # B (the bid VARCHAR (20), cid VARCHAR (20)) 
INSERT INTO # B 
SELECT 'b1', 'c1' UNION ALL 
SELECT 'b1', 'the c2' UNION ALL 
SELECT 'b1', 'c3' UNION ALL 
SELECT 'b2', 'c1' UNION ALL 
SELECT 'b2', 'c4' UNION ALL 
SELECT "b3", "c2" UNION ALL 
SELECT 'b3', 'c3' UNION ALL 
SELECT 'b3', 'c4' UNION ALL 
SELECT 'b4', 'the c2' 

The CREATE TABLE # A (id int, aname VARCHAR (20), aic VARCHAR (50)) 
INSERT INTO # A 
SELECT 1, "AAA", "b1" UNION ALL 
SELECT 2, 'BBB', 'b2' UNION ALL 
SELECT 3, 'CCC', 'b3' UNION ALL 
SELECT 4, 'DDD', 'b4' UNION ALL 
SELECT 5, 'EEE', 'b1'

# A.a IC=# B.b id
# # biggest id=C.i d
Output the following results:

Id aname acname
1 AAA A001 A002, A003
2 BBB A001, A004
3 CCC A002, A003
4 DDD A002
5 the EEE A001 A002, A003

CodePudding user response:

SELECT A.A NAME, STUFF ((SELECT ', '+ NAME FROM the JOIN # # B B C C ON biggest id=C.i d WHERE B.b id=A.A IC FOR XML PATH ('')), 1, 1, ' ')
The FROM # aa