Reputation: 323
I'm trying to combine multiple rows and multiple columns into one column. I've a table like below:
Parent table:
ID Name
1 A
2 B
Child table:
ID Score1 Score2 Remark1 Remark2
1 0 0 Good1! Excellent1!
1 0 0 Good1! Great1!
2 0 1 Good2! Excellent2!
Based on ID in both tables I want my output like below:
ID Name CountScore1+Score2 CombinedRemark
1 A 2 Good1!,Excellent1!;Good1!,Great1!
2 B 1 Good2!
Here what I've tried and it worked and for actual table there's 16 score column and remark column. Hopefully there's better clean approach in term of performance especially?
SELECT p.ID, p.Name,
CASE WHEN SUM(CASE WHEN c.Score1 = 0 THEN 1 ELSE 0 END) > 0 THEN 1 ELSE 0 END+
CASE WHEN SUM(CASE WHEN c.Score2 = 0 THEN 1 ELSE 0 END) > 0 THEN 1 ELSE 0 END [CountScore1+Score2],
CASE WHEN SUM(CASE WHEN c.Score1 = 0 THEN 1 ELSE 0 END) > 0 THEN ISNULL(STUFF((SELECT ', ' + Remark1 FROM Child c1 WHERE c1.ID = p.ID FOR XML PATH('')), 1, 1, ''), '') ELSE '' END+';'+ CASE WHEN SUM(CASE WHEN c.Score1 = 0 THEN 1 ELSE 0 END) > 0 THEN ISNULL(STUFF((SELECT ', ' + Remark2 FROM Child c1 WHERE c1.ID = p.ID FOR XML PATH('')), 1, 1, ''), '') ELSE '' END [CombinedRemark]
FROM Parent p
LEFT JOIN Child c ON c.ID = p.ID
GROUP BY p.ID, p.Name
Upvotes: 0
Views: 42