Concatenar valores de fila T-SQL
Estoy tratando de reunir algunos datos para un informe y necesito concatenar los valores de fila de una de las tablas. Aquí está la estructura básica de la tabla:
Reviews
ReviewID
ReviewDate
Revisores
ReviewerID
ReviewID
UserID
Usuarios
UserID
FName
LName
Esta es una relación M:M. Cada Revisión puede tener muchos Revisores; cada Usuario puede estar asociado con muchas Revisiones.
Básicamente, todo lo que quiero ver son reseñas.ReviewID, Reviews.Fecha de revisión, y un concatenado cadena de los nombresde todos los Usuarios asociados para esa Revisión (delimitada por comas).
En lugar de:
ReviewID---ReviewDate---User
1----------12/1/2009----Bob
1----------12/1/2009----Joe
1----------12/1/2009----Frank
2----------12/9/2009----Sue
2----------12/9/2009----Alice
Muestra esto:
ReviewID---ReviewDate----Users
1----------12/1/2009-----Bob, Joe, Frank
2----------12/9/2009-----Sue, Alice
He encontrado este artículo que describe algunas maneras de hacer esto, pero la mayoría de estos parecen tratar solo con una tabla, no múltiples; desafortunadamente, mi SQL-fu no es lo suficientemente fuerte como para adaptarlos a mis circunstancias. Estoy particularmente interesado en el ejemplo en ese sitio que utiliza PARA XML PATH() ya que parece el más limpio y muy directo.
SELECT p1.CategoryId,
( SELECT ProductName + ', '
FROM Northwind.dbo.Products p2
WHERE p2.CategoryId = p1.CategoryId
ORDER BY ProductName FOR XML PATH('')
) AS Products
FROM Northwind.dbo.Products p1
GROUP BY CategoryId;
¿Puede alguien echarme una mano con esto? Cualquier ayuda sería muy apreciada!
15 answers
Echa un vistazo a esto
DECLARE @Reviews TABLE(
ReviewID INT,
ReviewDate DATETIME
)
DECLARE @Reviewers TABLE(
ReviewerID INT,
ReviewID INT,
UserID INT
)
DECLARE @Users TABLE(
UserID INT,
FName VARCHAR(50),
LName VARCHAR(50)
)
INSERT INTO @Reviews SELECT 1, '12 Jan 2009'
INSERT INTO @Reviews SELECT 2, '25 Jan 2009'
INSERT INTO @Users SELECT 1, 'Bob', ''
INSERT INTO @Users SELECT 2, 'Joe', ''
INSERT INTO @Users SELECT 3, 'Frank', ''
INSERT INTO @Users SELECT 4, 'Sue', ''
INSERT INTO @Users SELECT 5, 'Alice', ''
INSERT INTO @Reviewers SELECT 1, 1, 1
INSERT INTO @Reviewers SELECT 2, 1, 2
INSERT INTO @Reviewers SELECT 3, 1, 3
INSERT INTO @Reviewers SELECT 4, 2, 4
INSERT INTO @Reviewers SELECT 5, 2, 5
SELECT *,
(
SELECT u.FName + ','
FROM @Users u INNER JOIN
@Reviewers rs ON u.UserID = rs.UserID
WHERE rs.ReviewID = r.ReviewID
FOR XML PATH('')
) AS Products
FROM @Reviews r
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-12-09 16:28:45
Resulta que hay una manera aún más fácil de hacer esto que no requiere un UDF:
select replace(replace(replace((cast((
select distinct columnName as X
from tableName
for xml path('')) as varchar(max))),
'</X><X>', ', '),'<X>', ''),'</X>','')
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-01-29 03:36:06
Tenía un problema similar y encontró una solución dulce después de jugar con el código durante 15 minutos
declare @result varchar(1000)
select @result = COALESCE(@result+','+A.col1, A.col1)
FROM ( select col1
from [table]
) A
select @result
Devuelve el resultado como value1,value2,value3, value4
Disfruta ;)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-01-31 06:57:36
SQLServer 2017 ahora tiene STRING_AGG que agrega varias cadenas en una usando un separador dado.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-08-31 10:41:01
Hay 3 maneras en que he tratado con los datos de enrollamiento, como usted ha descrito, 1.utilice un cursor, 2.utilice un UDF o 3. utilice un Agregado personalizado (escrito en. NET CLR).
El cursor y UDF son bastante lentos. (aprox. 0.1 seg por fila). El agregado personalizado CLR es sorprendentemente rápido. (aproximadamente 0,001 segundos por fila)
Microsoft envía el código (para hacer exactamente lo que desea) como parte del SDK para SQL 2005. Si lo tienes instalado, deberías poder encontrar el código en esta carpeta: C:\Program Files \ Microsoft SQL Server \ 90 \ Samples \ Engine \ Programmability \ CLR\StringUtilities. Es posible que también desee este artículo en MSDN. Habla de instalar el agregado personalizado y habilitarlo: http://msdn.microsoft.com/en-us/library/ms161551 (SQL. 90). aspx
Una vez que compile e instale el agregado personalizado, debería poder realizar consultas como esta:
SELECT Reviews.ReviewID, ReviewDate, dbo.StringUtilities.Concat(FName) AS [User]
FROM Reviews INNER JOIN Reviewers ON Reviews.ReviewID = Reviewers.ReviewID
INNER JOIN Users ON Reviews.UserID = Users.UserID
GROUP BY ReviewID, ReviewDate;
Y obtener un conjunto de resultados como se mostró (arriba)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-12-09 16:36:09
select p1.Availability ,COUNT(*),
(select name+',' from AdventureWorks2008.Production.Location p2 where
p1.Availability=p2.Availability for XML path(''),type).value('.','varchar(max)')
as Name from AdventureWorks2008.Production.Location p1 group by Availability
Resultado
Availability COUNT Name
---------------------------------------------------------------------------------
0.00 7 Tool Crib,Sheet Metal Racks,Paint Shop,Paint Storage,Metal
Storage,Miscellaneous Storage,Finished Goods Storage,
80.00 1 Specialized Paint,
96.00 1 Frame Forming,
108.00 1 Frame Welding,
120.00 4 Debur and Polish,Paint,Subassembly,Final Assembly,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-05-30 07:50:10
A UDF sería una buena manera de resolver esto.
Simplemente defina una función T-SQL (UDF) que toma un int param (ID de producto) y devuelve una cadena (concatenación de nombres asociados con el producto.) Si el nombre de su método es GetProductNames entonces su consulta podría tener este aspecto:
SELECT p1.CategoryId, dbo.GetProductNames(p1.CategoryId)
FROM Northwind.dbo.Products p1
GROUP BY CategoryId
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-12-09 16:41:07
Prueba esto:
Declare @Revs Table
(RevId int Priimary Key Not Null,
RevDt DateTime Null,
users varChar(1000) default '')
Insert @Revs (RevId, RevDt)
Select Distinct ReviewId, ReviewDate
From Reviews
Declare @UId Integer
Set @Uid = 0
While Exists (Select * From Users
Where UserID > @Uid)
Begin
Update @Revs Set
users = users + u.fName + ', '
From @Revs R
Join Reviewers uR On ur.ReviewId = R.RId
Join users u On u.UserId = uR.UserId
Where uR.UserId = @UId
Select @Uid = Min(UserId)
From users
Where UserId > @UId
End
Select * From @Revs
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-12-09 16:44:57
Select R.ReviewID, ReviewDate
, (Select FName + ', '
from Users
where UserID = R.UserID
order by FName FOR XML PATH(')
) as [Users]
from Reviews
inner join Reviewers AS R
On Reviews.ReviewID = R.ReviewID
Group By R.ReviewID, ReviewDate;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-12-09 16:52:21
Ahora desde SQL server 2017 hay una nueva función T-SQL llamada STRING_AGG
:
es una nueva función aggregate que concatena los valores de las expresiones de cadena y coloca valores separadores entre ellas.
El separador no se añade al final de la cadena.
Ejemplo:
SELECT STRING_AGG ( ISNULL(FirstName,'N/A'), ',') AS csv
FROM Person.Person;
El conjunto de resultados:
John,N/A,Mike,Peter,N/A,N/A,Alice,Bob
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-06-19 10:42:06
Parece que necesita la funcionalidad de group_concat (de mysql). esto se ha abordado aquí para otro conjunto de datos de prueba: ¿Cómo devolver múltiples valores en una columna (T-SQL)?
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-23 12:18:10
Cree una tabla temporal para volcar sus datos. A continuación, utilice el método FOR XML PATH. La consulta externa es necesaria para recortar la última coma de la lista.
CREATE TABLE #ReviewInfo (
ReviewId INT,
ReviewDate DATETIME,
Reviewer VARCHAR(1000))
INSERT INTO #ReviewInfo (ReviewId, ReviewDate, Reviewer)
SELECT r.ReviewId, r.ReviewDate, u.FName
FROM Reviews r
JOIN Reviewers rs ON r.ReviewId = rs.ReviewId
JOIN Users u ON u.UserId = rs.UserId
SELECT ReviewId, ReviewDate, LEFT(Users, LEN(Users)-1)
FROM (
SELECT ReviewId, ReviewDate,
(
SELECT Reviewer + ', '
FROM #ReviewInfo ri2
WHERE ri2.ReviewId = ri1.ReviewId
ORDER BY Reviewer
FOR XML PATH('')
) AS Users
FROM #ReviewInfo ri1
GROUP BY ReviewId, ReviewDate
) a
DROP TABLE #ReviewInfo
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-12-09 16:54:45
select
p1.Availability,
COUNT(*),
(
select name+','
from AdventureWorks2008.Production.Location p2
where p1.Availability=p2.Availability
for XML path(''),type
).value('.','varchar(max)') as Name
from AdventureWorks2008.Production.Location p1
group by Availability
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-05-08 02:18:01
Cuando el número de elementos es pequeño esto se puede hacer usando ROW_NUMBER () SOBRE LA PARTICIÓN POR:
declare @t table (col1 int, col2 varchar)
insert into @t VALUES (1,'A')
insert into @t VALUES (1,'B')
insert into @t VALUES (1,'C')
insert into @t VALUES (1,'D')
insert into @t VALUES (1,'E')
insert into @t VALUES (2,'X')
insert into @t VALUES (3,'Y')
select col1,
MAX(CASE seq WHEN 1 THEN col2 ELSE '' END ) +
MAX(CASE seq WHEN 2 THEN ', ' + col2 ELSE '' END ) +
MAX(CASE seq WHEN 3 THEN ', ' + col2 ELSE '' END ) +
MAX(CASE seq WHEN 4 THEN ', ' + col2 ELSE '' END ) +
MAX(CASE seq WHEN 5 THEN ',...' ELSE '' END )
as col2
from (
select col1, col2, ROW_NUMBER() OVER ( PARTITION BY col1 ORDER BY col2 ) seq
from @t
group by col1, col2
) x
group by col1
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-01-03 17:03:55
STRING_AGG ( expression, separator ) [ <order_clause> ]
<order_clause> ::=
WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] )
Vine a Stackoverflow buscando la función SQL server string aggregate.
La pregunta relevante había sido cerrada, marcada como un duplicado de esta pregunta, por lo que me veo obligado a responderla aquí o no responderla en absoluto.
Véase https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017 para más detalles.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-08-15 11:19:49