sql server - varias - Concatenar valores de columna para filas con los mismos valores(de diferentes columnas)
sql diferente de varios valores (3)
SQL Server 2005
Tengo una mesa que devuelve
ID name prop value
--------------------------
1 one Prop1 a
1 one Prop1 b
1 one Prop2 c
2 two Prop1 d
2 two Prop2 e
¿Cómo puedo ejecutar una selección para regresar?
ID name prop value
-----------------------------
1 one Prop1 a,b
1 one Prop2 c
2 two Prop1 d
2 two Prop2 e
prueba esto:
--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
SALIDA:
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)
SELECT T1.Name, T1.prop, T1.value+","+T2.value
From Table T1 INNER JOIN Table T2
ON T1.name = T2.name and T1.prop = T2.Prop and T1.value<>T2.value
Sin embargo, esto solo funcionará para dos valores. Avísame y puedo reescribir para N valores.
SELECT ID
,name
,prop
,STUFF((SELECT '','' + value FROM table t2 WHERE t2.ID = t1.ID and t2.name = t1.name AND t2.prop = t1.prop FOR XML PATH('''')),1,1,'''') AS value
FROM table t1
GROUP BY ID,name,prop