连接具有相同值(不同列)的行的列值 [英] Concatenate column values for rows with the same values (of different columns)

问题描述

SQL Server 2005

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

如何对其执行选择以返回

How can I run a select on it to return

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

推荐答案

尝试一下:

--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)

这篇关于连接具有相同值(不同列)的行的列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！