sql-server - 连接具有相同值(不同列)的行的列值

Question

SQL Server 2005

我有一张返回的表

ID  name    prop    value
--------------------------
1   one     Prop1   a
1   one     Prop1   b
1   one     Prop2   c
2   two     Prop1   d
2   two     Prop2   e

如何在其上运行选择以返回

ID  name    prop        value
-----------------------------
1   one     Prop1       a,b
1   one     Prop2       c
2   two     Prop1       d
2   two     Prop2       e

Answer 1

尝试这个:

--Concatenation with FOR XML and eleminating control/encoded character expansion "& < >"
set nocount on;
declare @YourTable table (RowID int, RowName varchar(5), prop varchar(5), RowValue varchar(5))

insert into @YourTable VALUES (1,'one','Prop1','a')
insert into @YourTable VALUES (1,'one','Prop1','b')
insert into @YourTable VALUES (1,'one','Prop2','c')
insert into @YourTable VALUES (2,'two','Prop1','d')
insert into @YourTable VALUES (2,'two','Prop2','e')
set nocount off

SELECT
    t1.RowID,t1.RowName,t1.Prop
        ,STUFF(
                   (SELECT
                        ', ' + t2.RowValue
                        FROM @YourTable t2
                        WHERE t1.RowID=t2.RowID AND t1.RowName=t2.RowName AND t1.Prop=t2.Prop
                        ORDER BY t2.RowValue
                        FOR XML PATH(''), TYPE
                   ).value('.','varchar(max)')
                   ,1,2, ''
              ) AS ChildValues
    FROM @YourTable t1
    GROUP BY t1.RowID,t1.RowName,t1.Prop

输出:

RowID       RowName Prop  ChildValues
----------- ------- ----- ------------
1           one     Prop1 a, b
1           one     Prop2 c
2           two     Prop1 d
2           two     Prop2 e

(4 row(s) affected)