Convierta filas en columnas usando 'Pivote' en SQL Server

Resuelto Lynn asked hace 11 años • 9 respuestas

He leído el material sobre las tablas dinámicas de MS y todavía tengo problemas para corregirlo.

Tengo una tabla temporal que se está creando, diremos que la columna 1 es un número de tienda, la columna 2 es un número de semana y, por último, la columna 3 es un total de algún tipo. Además, los números de la semana son dinámicos, los números de las tiendas son estáticos.

Store      Week     xCount
-------    ----     ------
102        1        96
101        1        138
105        1        37
109        1        59
101        2        282
102        2        212
105        2        78
109        2        97
105        3        60
102        3        123
101        3        220
109        3        87

Me gustaría que saliera como una tabla dinámica, así:

Store        1          2          3        4        5        6....
----- 
101        138        282        220
102         96        212        123
105         37        
109

Guarde los números en el costado y las semanas en la parte superior.

Lynn avatar Apr 10 '13 23:04 Lynn
Aceptado

Si está utilizando SQL Server 2005+, puede utilizar la PIVOTfunción para transformar los datos de filas en columnas.

Parece que necesitará usar SQL dinámico si se desconocen las semanas, pero es más fácil ver el código correcto usando inicialmente una versión codificada.

En primer lugar, aquí hay algunas definiciones de tablas rápidas y datos para usar:

CREATE TABLE yt 
(
  [Store] int, 
  [Week] int, 
  [xCount] int
);
    
INSERT INTO yt
(
  [Store], 
  [Week], [xCount]
)
VALUES
    (102, 1, 96),
    (101, 1, 138),
    (105, 1, 37),
    (109, 1, 59),
    (101, 2, 282),
    (102, 2, 212),
    (105, 2, 78),
    (109, 2, 97),
    (105, 3, 60),
    (102, 3, 123),
    (101, 3, 220),
    (109, 3, 87);

Si conoce sus valores, codificará la consulta:

select *
from 
(
  select store, week, xCount
  from yt 
) src
pivot
(
  sum(xcount)
  for week in ([1], [2], [3])
) piv;

Ver demostración de SQL

Luego, si necesitas generar el número de semana dinámicamente, tu código será:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(Week) 
                    from yt
                    group by Week
                    order by Week
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT store,' + @cols + ' from 
             (
                select store, week, xCount
                from yt
            ) x
            pivot 
            (
                sum(xCount)
                for week in (' + @cols + ')
            ) p '

execute(@query);

Consulte Demostración de SQL .

La versión dinámica genera la lista de weeknúmeros que deben convertirse en columnas. Ambos dan el mismo resultado:

| STORE |   1 |   2 |   3 |
---------------------------
|   101 | 138 | 282 | 220 |
|   102 |  96 | 212 | 123 |
|   105 |  37 |  78 |  60 |
|   109 |  59 |  97 |  87 |
Taryn avatar Apr 10 '2013 16:04 Taryn

Esto es para el número dinámico de semanas.

Ejemplo completo aquí: SQL Dynamic Pivot

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(Week)
FROM (SELECT DISTINCT Week FROM #StoreSales) AS Weeks

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
  N'SELECT Store, ' + @ColumnName + ' 
    FROM #StoreSales
    PIVOT(SUM(xCount) 
          FOR Week IN (' + @ColumnName + ')) AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery
Enkode avatar Dec 17 '2014 18:12 Enkode