Presta atención porque en este tutorial vas a hallar el resultado que buscas.Este enunciado ha sido probado por nuestros expertos para asegurar la calidad y exactitud de nuestro contenido.
Solución:
Hay varias formas de transformar estos datos. En tu publicación original, dijiste que PIVOT parece demasiado complejo para este escenario, pero se puede aplicar muy fácilmente utilizando tanto el UNPIVOT y PIVOT funciones en SQL Server.
Sin embargo, si no tiene acceso a esas funciones, puede replicarlo usando UNION ALL para UNPIVOT y luego una función agregada con un CASE declaración a PIVOT:
Crear mesa:
CREATE TABLE yourTable([color] varchar(5), [Paul] int, [John] int, [Tim] int, [Eric] int);
INSERT INTO yourTable
([color], [Paul], [John], [Tim], [Eric])
VALUES
('Red', 1, 5, 1, 3),
('Green', 8, 4, 3, 5),
('Blue', 2, 2, 9, 1);
Versión Union All, Agregada y CASE:
select name,
sum(case when color = 'Red' then value else 0 end) Red,
sum(case when color = 'Green' then value else 0 end) Green,
sum(case when color = 'Blue' then value else 0 end) Blue
from
(
select color, Paul value, 'Paul' name
from yourTable
union all
select color, John value, 'John' name
from yourTable
union all
select color, Tim value, 'Tim' name
from yourTable
union all
select color, Eric value, 'Eric' name
from yourTable
) src
group by name
Ver SQL Fiddle con demostración
los UNION ALL realiza el UNPIVOT de los datos transformando las columnas Paul, John, Tim, Eric en filas separadas. Luego aplica la función agregada sum() con el case declaración para obtener las nuevas columnas para cada color.
Versión estática sin pivote y pivote:
Ambos UNPIVOT y PIVOT Las funciones en el servidor SQL facilitan mucho esta transformación. Si conoce todos los valores que desea transformar, puede codificarlos en un static versión para obtener el resultado:
select name, [Red], [Green], [Blue]
from
(
select color, name, value
from yourtable
unpivot
(
value for name in (Paul, John, Tim, Eric)
) unpiv
) src
pivot
(
sum(value)
for color in ([Red], [Green], [Blue])
) piv
Ver SQL Fiddle con demostración
La consulta interna con el UNPIVOT realiza la misma función que el UNION ALL. Toma la lista de columnas y la convierte en filas, la PIVOT luego realiza la transformación final en columnas.
Versión dinámica de pivote:
Si tiene un número desconocido de columnas (Paul, John, Tim, Eric en su ejemplo) y luego un número desconocido de colores para transformar, puede usar sql dinámico para generar la lista para UNPIVOT y luego PIVOT:
DECLARE @colsUnpivot AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@colsPivot as NVARCHAR(MAX)
select @colsUnpivot = stuff((select ','+quotename(C.name)
from sys.columns as C
where C.object_id = object_id('yourtable') and
C.name <> 'color'
for xml path('')), 1, 1, '')
select @colsPivot = STUFF((SELECT ','
+ quotename(color)
from yourtable t
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query
= 'select name, '[email protected]+'
from
(
select color, name, value
from yourtable
unpivot
(
value for name in ('[email protected]+')
) unpiv
) src
pivot
(
sum(value)
for color in ('[email protected]+')
) piv'
exec(@query)
Ver SQL Fiddle con demostración
La versión dinámica consulta tanto yourtable y luego el sys.columns tabla para generar la lista de elementos a UNPIVOT y PIVOT. Esto luego se agrega a una consulta string para ser ejecutado. La ventaja de la versión dinámica es si tiene una lista cambiante de colors y / o names esto generará la lista en tiempo de ejecución.
Las tres consultas producirán el mismo resultado:
| NAME | RED | GREEN | BLUE |
-----------------------------
| Eric | 3 | 5 | 1 |
| John | 5 | 4 | 2 |
| Paul | 1 | 8 | 2 |
| Tim | 1 | 3 | 9 |
Esto normalmente requiere que conozca TODAS las etiquetas de columna Y fila de antemano. Como puede ver en la consulta a continuación, todas las etiquetas se enumeran en su totalidad en las operaciones UNPIVOT y (re) PIVOT.
Configuración del esquema de MS SQL Server 2012:
create table tbl (
color varchar(10), Paul int, John int, Tim int, Eric int);
insert tbl select
'Red' ,1 ,5 ,1 ,3 union all select
'Green' ,8 ,4 ,3 ,5 union all select
'Blue' ,2 ,2 ,9 ,1;
Consulta 1:
select *
from tbl
unpivot (value for name in ([Paul],[John],[Tim],[Eric])) up
pivot (max(value) for color in ([Red],[Green],[Blue])) p
Resultados:
| NAME | RED | GREEN | BLUE |
-----------------------------
| Eric | 3 | 5 | 1 |
| John | 5 | 4 | 2 |
| Paul | 1 | 8 | 2 |
| Tim | 1 | 3 | 9 |
Notas adicionales:
- Dado un nombre de tabla, puede determinar todos los nombres de columna de sys.columns o PARA engaños XML usando nombre local().
- También puede crear la lista de colores distintos (o valores para una columna) usando FOR XML.
- Lo anterior se puede combinar en un lote SQL dinámico para manejar cualquier tabla.
Me gustaría señalar algunas soluciones más para transponer columnas y filas en SQL.
El primero es usar CURSOR. Aunque el consenso general en la comunidad profesional es mantenerse alejado de los cursores de SQL Server, todavía hay casos en los que se recomienda el uso de cursores. De todos modos, los cursores nos presentan otra opción para transponer filas en columnas.
-
Expansión vertical
Similar al PIVOT, el cursor tiene la capacidad dinámica de agregar más filas a medida que su conjunto de datos se expande para incluir más números de política.
-
Expansión horizontal
A diferencia del PIVOT, el cursor sobresale en esta área ya que puede expandirse para incluir el documento recién agregado, sin alterar el script.
-
Desglose del rendimiento
La principal limitación de la transposición de filas a columnas usando CURSOR es una desventaja que está relacionada con el uso de cursores en general: tienen un costo de rendimiento significativo. Esto se debe a que el Cursor genera una consulta separada para cada operación FETCH NEXT.
Otra solución para transponer filas a columnas es mediante XML.
La solución XML para transponer filas a columnas es básicamente una versión óptima de PIVOT ya que aborda la limitación dinámica de columnas.
La versión XML del script aborda esta limitación mediante el uso de una combinación de XML Path, T-SQL dinámico y algunas funciones integradas (es decir, STUFF, QUOTENAME).
-
Expansión vertical
Al igual que el PIVOT y el Cursor, las políticas recién agregadas se pueden recuperar en la versión XML del script sin alterar el script original.
-
Expansión horizontal
A diferencia de PIVOT, los documentos recién agregados se pueden mostrar sin alterar el script.
-
Desglose del rendimiento
En términos de IO, las estadísticas de la versión XML del script son casi similares a las de PIVOT; la única diferencia es que el XML tiene un segundo escaneo de la tabla dtTranspose, pero esta vez desde una caché de datos de lectura lógica.
Puede encontrar más sobre estas soluciones (incluidos algunos ejemplos reales de T-SQL) en este artículo: https://www.sqlshack.com/multiple-options-to-transposing-rows-into-columns/
valoraciones y comentarios
Recuerda que tienes permiso de valorar esta crónica si acertaste tu dilema a tiempo.