pasar - Convertir filas en columnas usando ''Pivot'' en SQL Server

pivot y unpivot sql server

Question

Bueno, supongo que soy muy densa. He leído las cosas en las tablas dinámicas de MS y todavía tengo problemas para hacer esto correctamente. He visto a algunos de ustedes que parecen comer y dormir estas cosas, así que decidí registrarme y hacer la pregunta.

Tengo una tabla temporal que se está creando, diremos que la columna 1 es un número de tienda y la columna 2 es un número de semana y, por último, la columna 3 es un total de algún tipo. También los números de semana son dinámicos, los números de tienda 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, como esta:

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

Almacena los números por el lado y las semanas en la parte superior.

Gracias por la ayuda.

Answer 1

Aquí hay una revisión de la respuesta de @Tayrn que puede ayudarlo a comprender cómo pivotar un poco más fácilmente:

Puede que esta no sea la mejor manera de hacerlo, pero esto es lo que me ayudó a comprender cómo hacer pivotar las tablas.

ID = filas que desea pivotar

MY_KEY = la columna que está seleccionando de la tabla original que contiene los nombres de columna que desea pivotar.

VAL = el valor que desea devolver en cada columna.

MAX (VAL) => Se puede reemplazar con otras funciones agregadas. SUM (VAL), MIN (VAL), ETC ...

DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT '','' + QUOTENAME(MY_KEY)
from yt
group by MY_KEY
order by MY_KEY ASC
FOR XML PATH(''''), TYPE
).value(''.'', ''NVARCHAR(MAX)'')
,1,1,'''')
set @query = ''SELECT ID,'' + @cols + '' from
(
select ID, MY_KEY, VAL
from yt
) x
pivot
(
sum(VAL)
for MY_KEY in ('' + @cols + '')
) p ''
execute(@query);

Answer 2

Esto es lo que puedes hacer:

SELECT *
FROM yourTable
PIVOT (MAX(xCount)
FOR Week in ([1],[2],[3],[4],[5],[6],[7])) AS pvt

DEMO

Answer 3

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

Answer 4

Estoy escribiendo un sp que podría ser útil para este propósito, básicamente este sp pivotea cualquier tabla y devuelve una nueva tabla pivotada o devuelve solo el conjunto de datos, esta es la forma de ejecutarlo:

Exec dbo.rs_pivot_table @schema=dbo,@table=table_name,@column=column_to_pivot,@agg=''sum([column_to_agg]),avg([another_column_to_agg]),'',
@sel_cols=''column_to_select1,column_to_select2,column_to_select1'',@new_table=returned_table_pivoted;

tenga en cuenta que en el parámetro @agg los nombres de columna deben estar con ''['' y el parámetro debe terminar con una coma '',''

SP

Create Procedure [dbo].[rs_pivot_table]
@schema sysname=dbo,
@table sysname,
@column sysname,
@agg nvarchar(max),
@sel_cols varchar(max),
@new_table sysname,
@add_to_col_name sysname=null
As
--Exec dbo.rs_pivot_table dbo,##TEMPORAL1,tip_liq,''sum([val_liq]),sum([can_liq]),'',''cod_emp,cod_con,tip_liq'',##TEMPORAL1PVT,''hola'';
Begin
Declare @query varchar(max)='''';
Declare @aggDet varchar(100);
Declare @opp_agg varchar(5);
Declare @col_agg varchar(100);
Declare @pivot_col sysname;
Declare @query_col_pvt varchar(max)='''';
Declare @full_query_pivot varchar(max)='''';
Declare @ind_tmpTbl int; --Indicador de tabla temporal 1=tabla temporal global 0=Tabla fisica
Create Table #pvt_column(
pivot_col varchar(100)
);
Declare @column_agg table(
opp_agg varchar(5),
col_agg varchar(100)
);
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@table) AND type in (N''U''))
Set @ind_tmpTbl=0;
ELSE IF OBJECT_ID(''tempdb..''+ltrim(rtrim(@table))) IS NOT NULL
Set @ind_tmpTbl=1;
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@new_table) AND type in (N''U'')) OR
OBJECT_ID(''tempdb..''+ltrim(rtrim(@new_table))) IS NOT NULL
Begin
Set @query=''DROP TABLE ''+@new_table+'''';
Exec (@query);
End;
Select @query=''Select distinct ''+@column+'' From ''+(case when @ind_tmpTbl=1 then ''tempdb.'' else '''' end)+@schema+''.''+@table+'' where ''+@column+'' is not null;'';
Print @query;
Insert into #pvt_column(pivot_col)
Exec (@query)
While charindex('','',@agg,1)>0
Begin
Select @aggDet=Substring(@agg,1,charindex('','',@agg,1)-1);
Insert Into @column_agg(opp_agg,col_agg)
Values(substring(@aggDet,1,charindex(''('',@aggDet,1)-1),ltrim(rtrim(replace(substring(@aggDet,charindex(''['',@aggDet,1),charindex('']'',@aggDet,1)-4),'')'',''''))));
Set @agg=Substring(@agg,charindex('','',@agg,1)+1,len(@agg))
End
Declare cur_agg cursor read_only forward_only local static for
Select
opp_agg,col_agg
from @column_agg;
Open cur_agg;
Fetch Next From cur_agg
Into @opp_agg,@col_agg;
While @@fetch_status=0
Begin
Declare cur_col cursor read_only forward_only local static for
Select
pivot_col
From #pvt_column;
Open cur_col;
Fetch Next From cur_col
Into @pivot_col;
While @@fetch_status=0
Begin
Select @query_col_pvt=''isnull(''+@opp_agg+''(case when ''+@column+''=''+quotename(@pivot_col,char(39))+'' then ''+@col_agg+
'' else null end),0) as [''+lower(Replace(Replace(@opp_agg+''_''+convert(varchar(100),@pivot_col)+''_''+replace(replace(@col_agg,''['',''''),'']'',''''),'' '',''''),''&'',''''))+
(case when @add_to_col_name is null then space(0) else ''_''+isnull(ltrim(rtrim(@add_to_col_name)),'''') end)+'']''
print @query_col_pvt
Select @full_query_pivot=@full_query_pivot+@query_col_pvt+'', ''
--print @full_query_pivot
Fetch Next From cur_col
Into @pivot_col;
End
Close cur_col;
Deallocate cur_col;
Fetch Next From cur_agg
Into @opp_agg,@col_agg;
End
Close cur_agg;
Deallocate cur_agg;
Select @full_query_pivot=substring(@full_query_pivot,1,len(@full_query_pivot)-1);
Select @query=''Select ''+@sel_cols+'',''+@full_query_pivot+'' into ''+@new_table+'' From ''+(case when @ind_tmpTbl=1 then ''tempdb.'' else '''' end)+
@schema+''.''+@table+'' Group by ''+@sel_cols+'';'';
print @query;
Exec (@query);
End;
GO

Este es un ejemplo de ejecución:

Exec dbo.rs_pivot_table @schema=dbo,@table=##TEMPORAL1,@column=tip_liq,@agg=''sum([val_liq]),avg([can_liq]),'',@sel_cols=''cod_emp,cod_con,tip_liq'',@new_table=##TEMPORAL1PVT;

entonces Select * From ##TEMPORAL1PVT devolvería:

Answer 5

He logrado lo mismo antes utilizando subconsultas. Entonces, si su tabla original se llamaba StoreCountsByWeek, y usted tenía una tabla separada que enumeraba las ID de las tiendas, entonces se vería así:

SELECT StoreID,
Week1=(SELECT ISNULL(SUM(xCount),0) FROM StoreCountsByWeek WHERE StoreCountsByWeek.StoreID=Store.StoreID AND Week=1),
Week2=(SELECT ISNULL(SUM(xCount),0) FROM StoreCountsByWeek WHERE StoreCountsByWeek.StoreID=Store.StoreID AND Week=2),
Week3=(SELECT ISNULL(SUM(xCount),0) FROM StoreCountsByWeek WHERE StoreCountsByWeek.StoreID=Store.StoreID AND Week=3)
FROM Store
ORDER BY StoreID

Una ventaja de este método es que la sintaxis es más clara y facilita la unión a otras tablas para incluir otros campos en los resultados.

Mis resultados anecdóticos son que la ejecución de esta consulta en un par de miles de filas se completó en menos de un segundo, y en realidad tuve 7 subconsultas. Pero como se señala en los comentarios, es más costoso computacional hacerlo de esta manera, así que tenga cuidado al usar este método si espera que se ejecute en grandes cantidades de datos.

Answer 6

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

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

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 sus valores son conocidos, entonces codificará la consulta:

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

Ver Demo SQL

Entonces, si necesita generar el número de la semana dinámicamente, su 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);

Ver Demo SQL .

La versión dinámica, genera la lista de números de week 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 |

Answer 7

select * from (select name, ID from Empoyee) Visits
pivot(sum(ID) for name
in ([Emp1],
[Emp2],
[Emp3]
) ) as pivottable;