31 votos

Concatenar los valores de fila de T-SQL

Estoy tratando de reunir algunos datos para un informe y la necesidad de concatenar los valores de la fila de una de las tablas. Aquí es el básico de la estructura de la tabla:

Comentarios

 ReviewID  
 ReviewDate

Los revisores

 ReviewerID  
 ReviewID  
 UserID

Los usuarios

UserID  
FName  
LName

Este es un M:M relación. Cada Examen puede tener muchos críticos; cada Usuario puede estar asociada con muchos Comentarios.

Básicamente, todo lo que quiero es ver los Comentarios.ReviewID, Comentarios.ReviewDate, y una cadena concatenada de la FName de todos los Usuarios asociados para que de la Revisión (delimitado 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

Mostrar esto:

ReviewID---ReviewDate----Users  
1----------12/1/2009-----Bob, Joe, Frank  
2----------12/9/2009-----Sue, Alice

He encontrado este artículo se describen algunas maneras de hacer esto, pero la mayoría de estos parece que sólo se ocupan de una tabla, no varios; por desgracia, my SQL-fu no es lo suficientemente fuerte como para adaptar estos a mis circunstancias. Estoy particularmente interesado en el ejemplo en que sitio en el que se utiliza PARA la PATHXML() como que se ve más limpia y recta hacia adelante.

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 darme una mano con esto? Cualquier ayuda sería muy apreciada!

33voto

astander Puntos 83138

Echa un vistazo a este

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

20voto

Resulta que hay una manera más fácil de hacer esto que no requiere de una UDF:

select replace(replace(replace((cast((
        select distinct columnName as X
        from tableName 
        for xml path('')) as varchar(max))), 
   '</X><X>', ', '),'<X>', ''),'</X>','')

10voto

Talha Puntos 132

Tenido un problema similar y encontró una solución dulce después de jugar con el código de 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 valor1,valor2,valor3,value4

Disfrutar ;)

6voto

TimG Puntos 447

Hay 3 maneras he tratado con material de seguridad de los datos, como la ha descrito, 1.el uso de un cursor, 2.el uso de una UDF o 3. utilice el un Agregado Personalizado (escrito en .NET CLR).
El Cursor y UDF son bastante lentos. (aproximadamente 0.1 segundos en cada fila). El CLR agregado personalizado es sorprendentemente rápido. (aprox 0.001 segundos en cada fila)

Microsoft lanza el código (para hacer exactamente lo que quieres) como parte del SDK para SQL 2005. Si usted tiene instalado, usted debería ser capaz de encontrar el código en esta carpeta: C:\Program Files\Microsoft SQL Server\90\Samples\Engine\Programación\CLR\StringUtilities. También puede ser que desee a este artículo de MSDN. Habla acerca de cómo instalar el agregado personalizado y permitiéndole: http://msdn.microsoft.com/en-us/library/ms161551%28SQL.90%29.aspx

Una vez que compilar e instalar el agregado personalizado, usted debería ser capaz de consulta 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 demostró (arriba)

5voto

pradeep-mohana Puntos 74
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,

Iteramos.com

Iteramos es una comunidad de desarrolladores que busca expandir el conocimiento de la programación mas allá del inglés.
Tenemos una gran cantidad de contenido, y también puedes hacer tus propias preguntas o resolver las de los demás.

Powered by:

X