xChaax
xChaax

Reputation: 323

Multiple case expression and stuff functions in select statement cause very bad performance

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

Answers (0)

Related Questions