one - stuff sql server
Sql PIVOT y agregado de concatenación de cadenas (4)
Me gustaría utilizar una consulta SQL dinámica para construir una tabla de resultados donde el texto concatenar como resultado dentro de la sección DATOS de la tabla dinámica.
es decir, tengo el siguiente resultado al usar un simple seleccionar:
+------------+-----------------+---------------+ | Event Name | Resource Type | Resource Name | +------------+-----------------+---------------+ | Event 1 | Resource Type 1 | Resource 1 | | Event 1 | Resource Type 1 | Resource 2 | | Event 1 | Resource Type 2 | Resource 3 | | Event 1 | Resource Type 2 | Resource 4 | | Event 1 | Resource Type 3 | Resource 5 | | Event 1 | Resource Type 3 | Resource 6 | | Event 1 | Resource Type 3 | Resource 7 | | Event 1 | Resource Type 4 | Resource 8 | | Event 2 | Resource Type 5 | Resource 1 | | Event 2 | Resource Type 2 | Resource 3 | | Event 2 | Resource Type 3 | Resource 11 | | Event 2 | Resource Type 3 | Resource 12 | | Event 2 | Resource Type 3 | Resource 13 | | Event 2 | Resource Type 4 | Resource 14 | | Event 2 | Resource Type 5 | Resource 9 | | Event 2 | Resource Type 5 | Resource 16 | +------------+-----------------+---------------+
Y me gustaría construir una consulta de resultados que se vería así:
+---------------------+------------------------+------------------------+---------------------------------------+-----------------+-------------------------------------+ | Event/Resource Type | Resource Type 1 | Resource Type 2 | Resource Type 3 | Resource Type 4 | Resource Type 5 | +---------------------+------------------------+------------------------+---------------------------------------+-----------------+-------------------------------------+ | Event 1 | Resource 1, Resource 2 | Resource 3, Resource 4 | Resource 5, Resource 6, Resource 7 | Resource 8 | NULL | | Event 2 | NULL | Resource 3 | Resource 11, Resource 12, Resource 13 | Resource 14 | Resource 1, Resource 9, Resource 16 | +---------------------+------------------------+------------------------+---------------------------------------+-----------------+-------------------------------------+
Sé cómo usar una instrucción PIVOT en ms-sql, pero no sé cómo agregar el Nombre del recurso en una concatenación de elementos separados por comas para cada tipo de recurso.
PD: También podría usar una solución usando el Martix provisto por SSRS 2008-R2 usando Report Builde 3 con la primera tabla como mi conjunto de datos y crear una matriz que agregue los nombres de los recursos en una cadena separada por comas.
En el generador de informes, debe usar el asistente Tabla o Matriz y hacer lo siguiente:
-
Resource Typecampo como grupos de columnas. -
Event Namecampo como los grupos de fila. - Y el campo
Resource Namecomo detalles, tendrá que usar una función agregada comoCount.
En esta etapa, complete el asistente y, una vez completado, edite la celda Resource Name del Resource Name como una expresión. Reemplaza la expresión con:
=Join( LookupSet( Fields!EVENT_NAME.Value + Fields!RESOURCE_TYPE.Value,
Fields!EVENT_NAME.Value + Fields!RESOURCE_TYPE.Value,
Fields!RESOURCE_NAME.Value, "DataSet1"), ", ")
Ahora probado y probado:
Ejemplo completo de trabajo:
SET NOCOUNT ON
GO
DECLARE @SourceTable TABLE
(
EventName NVARCHAR(10)
,ResourceType NVARCHAR(20)
,ResourceName NVARCHAR(20)
)
INSERT INTO @SourceTable(EventName,ResourceType,ResourceName)
VALUES (''Event 1'',''Resource Type 1'',''Resource 1'')
,(''Event 1'',''Resource Type 1'',''Resource 2'')
,(''Event 1'',''Resource Type 2'',''Resource 3'')
,(''Event 1'',''Resource Type 2'',''Resource 4'')
,(''Event 1'',''Resource Type 3'',''Resource 5'')
,(''Event 1'',''Resource Type 3'',''Resource 6'')
,(''Event 1'',''Resource Type 3'',''Resource 7'')
,(''Event 1'',''Resource Type 4'',''Resource 8'')
,(''Event 2'',''Resource Type 5'',''Resource 1'')
,(''Event 2'',''Resource Type 2'',''Resource 3'')
,(''Event 2'',''Resource Type 3'',''Resource 11'')
,(''Event 2'',''Resource Type 3'',''Resource 12'')
,(''Event 2'',''Resource Type 3'',''Resource 13'')
,(''Event 2'',''Resource Type 4'',''Resource 14'')
,(''Event 2'',''Resource Type 5'',''Resource 9'')
,(''Event 2'',''Resource Type 5'',''Resource 16'')
;WITH SourceTable AS
(
SELECT DISTINCT ST1.EventName
,ST1.ResourceType
,(SELECT SUBSTRING((SELECT '','' +ResourceName
FROM @SourceTable AS ST2
WHERE ST1.EventName=ST2.EventName AND ST1.ResourceType=ST2.ResourceType
FOR XML PATH('''')),2,200) AS CSV) AS ResourceName
FROM @SourceTable AS ST1
)
SELECT EventName
,[Resource Type 1]
,[Resource Type 2]
,[Resource Type 3]
,[Resource Type 4]
,[Resource Type 5]
FROM
(
SELECT EventName
,ResourceType
,ResourceName
FROM SourceTable
) PivotSource
PIVOT
(
MAX(ResourceName) FOR ResourceType IN ([Resource Type 1],[Resource Type 2],[Resource Type 3],[Resource Type 4],[Resource Type 5])
) PivotTable
SET NOCOUNT OFF
GO
Esto funciona para mí en SQL 2008, y es dinámico; manejará un Resource Type adicional
IF OBJECT_ID(''tempdb..#test'') IS NOT NULL
DROP TABLE #test
GO
CREATE TABLE #test
(
eventName VARCHAR(30),
resourceType VARCHAR(30),
resourceName VARCHAR(30)
);
INSERT INTO #test
VALUES (''Event 1'',''Resource Type 1'',''Resource 1''),
(''Event 1'',''Resource Type 1'',''Resource 2''),
(''Event 1'',''Resource Type 2'',''Resource 3''),
(''Event 1'',''Resource Type 2'',''Resource 4''),
(''Event 1'',''Resource Type 3'',''Resource 5''),
(''Event 1'',''Resource Type 3'',''Resource 6''),
(''Event 1'',''Resource Type 3'',''Resource 7''),
(''Event 1'',''Resource Type 4'',''Resource 8''),
(''Event 2'',''Resource Type 5'',''Resource 1''),
(''Event 2'',''Resource Type 2'',''Resource 3''),
(''Event 2'',''Resource Type 3'',''Resource 11''),
(''Event 2'',''Resource Type 3'',''Resource 12''),
(''Event 2'',''Resource Type 3'',''Resource 13''),
(''Event 2'',''Resource Type 4'',''Resource 14''),
(''Event 2'',''Resource Type 5'',''Resource 9''),
(''Event 2'',''Resource Type 5'',''Resource 16'');
DECLARE @resourceTypes VARCHAR(max);
SELECT @resourceTypes = stuff((SELECT DISTINCT '',['' + resourceType + '']''
FROM #test
FOR xml path('''')), 1, 1, '''');
DECLARE @query NVARCHAR(max);
SET @query = ''SELECT *
FROM (SELECT eventName,
resourceType,
stuff((SELECT '''','''' + resourceName + ''''''''
FROM #test b
WHERE a.eventName = b.eventName
AND a.resourceType = b.resourceType
FOR xml path('''''''')), 1, 1, '''''''') resourceName
FROM #test a
GROUP BY eventName,
resourceType) AS data PIVOT (max(resourceName) FOR resourceType IN ('' + @resourceTypes + '')) AS pvt'';
EXEC(@query);
DROP TABLE #test;
Para obtener el resultado, primero debe concatenar los valores en la lista separada por comas.
Yo usaría CROSS APPLY y FOR XML PATH :
SELECT distinct e.[Event Name],
e.[Resource Type],
LEFT(r.ResourceName , LEN(r.ResourceName)-1) ResourceName
FROM yourtable e
CROSS APPLY
(
SELECT r.[Resource Name] + '', ''
FROM yourtable r
where e.[Event Name] = r.[Event Name]
and e.[Resource Type] = r.[Resource Type]
FOR XML PATH('''')
) r (ResourceName)
Ver SQL Fiddle con demostración . El te da resultado:
| EVENT NAME | RESOURCE TYPE | RESOURCENAME |
------------------------------------------------------------------------
| Event 1 | Resource Type 1 | Resource 1, Resource 2 |
| Event 1 | Resource Type 2 | Resource 3, Resource 4 |
| Event 1 | Resource Type 3 | Resource 5, Resource 6, Resource 7 |
| Event 1 | Resource Type 4 | Resource 8 |
| Event 2 | Resource Type 2 | Resource 3 |
| Event 2 | Resource Type 3 | Resource 11, Resource 12, Resource 13 |
| Event 2 | Resource Type 4 | Resource 14 |
| Event 2 | Resource Type 5 | Resource 1, Resource 9, Resource 16 |
Luego aplicará su PIVOT a este resultado:
SELECT [Event Name],
[Resource Type 1], [Resource Type 2],
[Resource Type 3], [Resource Type 4],
[Resource Type 5]
FROM
(
SELECT distinct e.[Event Name],
e.[Resource Type],
LEFT(r.ResourceName , LEN(r.ResourceName)-1) ResourceName
FROM yourtable e
CROSS APPLY
(
SELECT r.[Resource Name] + '', ''
FROM yourtable r
where e.[Event Name] = r.[Event Name]
and e.[Resource Type] = r.[Resource Type]
FOR XML PATH('''')
) r (ResourceName)
) src
pivot
(
max(ResourceName)
for [Resource Type] in ([Resource Type 1], [Resource Type 2],
[Resource Type 3], [Resource Type 4],
[Resource Type 5])
) piv
Ver SQL Fiddle con demostración . Su resultado final será:
| EVENT NAME | RESOURCE TYPE 1 | RESOURCE TYPE 2 | RESOURCE TYPE 3 | RESOURCE TYPE 4 | RESOURCE TYPE 5 |
----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Event 1 | Resource 1, Resource 2 | Resource 3, Resource 4 | Resource 5, Resource 6, Resource 7 | Resource 8 | (null) |
| Event 2 | (null) | Resource 3 | Resource 11, Resource 12, Resource 13 | Resource 14 | Resource 1, Resource 9, Resource 16 |