问SQL Server与GROUP BY连接

EN

DECLARE @MYTAB TABLE (ID INT,
                      ONE NVARCHAR(1),
                      TWO NVARCHAR(1)
                     )

INSERT INTO @MYTAB VALUES
(1, 'a', 'd'),
(2, 'a', 'd'),
(3, 'b', 'd'),
(4, 'b', 'd'),
(5, 'c', 'e'),
(6, 'c', 'e')


SELECT STUFF(
(
    SELECT N', '+CAST(ID AS NVARCHAR(50))
    FROM @MYTAB T2
    WHERE T2.ONE = T.ONE
          AND T2.TWO = T.TWO FOR XML PATH(''), TYPE
).value('text()[1]', 'nvarchar(max)'), 1, 2, N'') [MY AGGREGATE],
       ONE,
       TWO
FROM @MYTAB T
GROUP BY T.ONE,
         T.TWO;

SELECT  STRING_AGG(CAST(ID AS nvarchar(50)), ', '),
        ONE,
        TWO
FROM @MYTAB
GROUP BY ONE, TWO

AGG     ONE TWO
---------------
1, 2    a   d
3, 4    b   d
5, 6    c   e

create table #t 
(
    a char(1)
    ,b char(1)
    ,c char(1) 
)

insert into #t
select '1','a','d' union all 
select '2','a','d' union all 
select '3','b','d' union all 
select '4','b','d' union all 
select '5','c','e' union all 
select '6','c','e' 

select stuff ((select ','+ a FROM #t T1 where T1.b = T.B and T1.c = T.c For XML  PATH ('')) ,1,1,''),b,c
FROM #t T 
group by b,c 

declare @t table (a int, b varchar(1), c varchar(1))
insert into @t values (1, 'a', 'd'), (2, 'a', 'd'), (3, 'b', 'd'), (4, 'b', 'd'), (5, 'c', 'e'), (6, 'c', 'e')

select left(temp.a, len(temp.a) - 1) as a, 
       temp.b, 
       temp.c
from   ( select t.b, 
                t.c,
                ( select convert(varchar, t2.a) + ',' AS [text()]
                  from   @t t2
                  where  t2.b = t.b
                  and    t2.c = t.c
                  For XML PATH ('')
                ) as a
         from   @t t
         group by t.b, t.c
       ) temp