在sql中连接多个同名的行?

I can't seem to get the output I need with this, can anyone see what I am doing wrong?

我似乎无法得到我需要的输出，有人能看出我做错了什么吗?

Output should be

输出应该

1 | Adam | Math, Science
2 | Tony | Math
3 | Erica | English, History

But I keep resulting with

但是我继续

1 | Adam | Math, Science, English, History
2 | Tony | Math, Science, English, History
3 | Erica | Math, Science, English, History

CREATE TABLE T
(
    ID INT,
    WM VARCHAR(50),
    Class VARCHAR(50),
    Prof VARCHAR(50),
)

INSERT INTO T (ID, WM, Class, Prof)
VALUES
    (NULL, 'Adam', 'Math', 'Sam'),
    (1, 'Adam', 'Science', 'Marc'),
    (2, 'Tony','Math', 'Sam'),
    (3, 'Erica','English', 'P'),
    (3, 'Erica','History', 'P')


SELECT *, STUFF((
            SELECT DISTINCT ', ' + Class
            FROM T
            WHERE T.WM = T.WM
            FOR XML PATH('')), 1, 2, '')
FROM (
    SELECT DISTINCT WM
    FROM T
) T

Please leave a reason why, if leaving negative results

如果留下消极的结果，请留下原因

2 个解决方案

#1

The issue is in where T.WM = T.WM statement

问题出在T。WM = T。WM声明

SELECT *, STUFF((
            SELECT DISTINCT ', ' + Class
            FROM T as T2
            WHERE T.WM = T2.WM
            FOR XML PATH('')), 1, 2, '')
FROM (
    SELECT DISTINCT WM
    FROM T
) T

#2

I would use CTE:

我将使用CTE:

with cte as (
    select *, max(id) over (PARTITION by wm) NewId
    from t
)
SELECT NewId, WM, STUFF((
            SELECT DISTINCT ', ' + Class
            FROM cte
            WHERE C.WM = WM
            FOR XML PATH('')), 1, 2, '')
FROM cte c
group by NewId, WM

Check Demo

检查演示

#1