分组后合并多行的 sql 怎么写？大神请进。。。。。

Question

需求说明：
1. 相同的人，每条记录里这个三个字段  score1, score2, score3 只会有一个有值，如下表中的张三，李四，王五
2. 如果此人的 score1, score2, score3 这三个字段呈互斥分布，即该人的每个字段都只有一个值，如下表中的张三，则合并该人的这个三个值为一行记录，如期望结果中的张三；
3. 如果此人的 score1, score2, score3 这三个字段含有多个值，则保留该人的所有记录，如下表中的王五求大神帮助写一个 sql, 实现期望的结果非常感谢！！！！！！！id       name        score1   score2   score3
1        张三        10       0        0
2        张三        0        20       0
3        张三        0        0        30
4        李四        10       0        0
5        李四        0        20       0
6        王五        10       0        0
7        王五        20       0        0
8        王五        0        0        30
9        王五        10       0        0
期望的结果：
id       name        score1    score2    score3
1        张三        10        20        30
2        李四        10        20        0
3        王五        10        0         0
4        王五        20        0         0
5        王五        0         0         30
6        王五        10        0         0
            

Answer 1

SELECT
a. NAME,
a.score1,
b.score2,
c.score3
FROM
test a
JOIN test b
JOIN test c ON a. NAME = b. NAME
AND a. NAME = c. NAME
WHERE
a.score1 = 10
AND b.score2 = 20
AND c.score3 = 30
UNION
SELECT
a. NAME,
a.score1,
b.score2,
c.score3
FROM
test a
JOIN test b
JOIN test c ON a. NAME = b. NAME
AND a. NAME = c. NAME
WHERE
a. NAME = '李四'
AND a.score1 = 10
AND b.score2 = 20
UNION
SELECT
NAME,
score1,
score2,
score3
FROM
test
WHERE
NAME = '王五';

Answer 2

--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[name] nvarchar(22),[score1] int,[score2] int,[score3] int)
Insert #T
select 1,N'张三',10,0,0 union all
select 2,N'张三',0,20,0 union all
select 3,N'张三',0,0,30 union all
select 4,N'李四',10,0,0 union all
select 5,N'李四',0,20,0 union all
select 6,N'王五',10,0,0 union all
select 7,N'王五',20,0,0 union all
select 8,N'王五',0,0,30 union all
select 9,N'王五',10,0,0
Go
--测试数据结束
SELECT  a.name ,
        MAX(a.score1) AS score1 ,
        MAX(a.score2) AS score2 ,
        MAX(a.score3) AS score3
FROM    #T a
WHERE   name NOT IN ( SELECT    [name]
                      FROM      #T
                      GROUP BY  name
                      HAVING    SUM(CASE WHEN score1 <> 0 THEN 1
                                         ELSE 0
                                    END) > 1
                                OR SUM(CASE WHEN score2 <> 0 THEN 1
                                            ELSE 0
                                       END) > 1
                                OR SUM(CASE WHEN score3 <> 0 THEN 1
                                            ELSE 0
                                       END) > 1 )
   GROUP BY a.name
UNION
SELECT  b.name,b.score1,b.score2,b.score3
FROM    #T b
WHERE   b.name IN ( SELECT  [name]
                    FROM    #T
                    GROUP BY name
                    HAVING  SUM(CASE WHEN score1 <> 0 THEN 1
                                     ELSE 0
                                END) > 1
                            OR SUM(CASE WHEN score2 <> 0 THEN 1
                                        ELSE 0
                                   END) > 1
                            OR SUM(CASE WHEN score3 <> 0 THEN 1
                                        ELSE 0
                                   END) > 1 );