Converti righe in colonne usando ‘Pivot’ in SQL Server

Beh, immagino di essere molto denso. Ho letto le cose sulle tabelle pivot di MS e ho ancora problemi a ottenere questo corretto. Ho visto alcuni di voi che sembrano mangiare e dormire questa roba, quindi ho deciso di iscrivermi e fare la domanda.

Ho una tabella temporanea che viene creata, diremo che la colonna 1 è un numero di negozio e la colonna 2 è un numero di settimana e infine la colonna 3 è un totale di un certo tipo. Anche i numeri della settimana sono dinamici, i numeri dei negozi sono statici.

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 

Mi piacerebbe che venisse fuori come una tabella pivot, come questa:

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

Memorizza i numeri in basso e le settimane in alto.

Grazie per l’aiuto.

Se si utilizza SQL Server 2005+, è ansible utilizzare la funzione PIVOT per trasformare i dati da righe in colonne.

Sembra che dovrai usare sql dinamico se le settimane sono sconosciute ma è più facile vedere il codice corretto usando inizialmente una versione hard-coded.

Innanzitutto, ecco alcune definizioni e dati di tabelle veloci da utilizzare:

 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); 

Se i tuoi valori sono noti, dovrai codificare la query con hard-code:

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

Vedi Demo SQL

Quindi se hai bisogno di generare il numero della settimana in modo dinamico, il tuo codice sarà:

 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); 

Vedi Demo SQL .

La versione dynamic, genera l’elenco dei numeri delle week che dovrebbero essere convertiti in colonne. Entrambi danno lo stesso risultato:

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

Questo è per # dinamici di settimane.

Esempio completo qui: 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 

Ho raggiunto la stessa cosa prima usando le subquery. Quindi se la tua tabella originale si chiamava StoreCountsByWeek, e avevi una tabella separata che elencava gli ID Store, allora sarebbe simile a questa:

 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 

Un vantaggio di questo metodo è che la syntax è più chiara e rende più facile unirsi ad altre tabelle per inserire anche altri campi nei risultati.

I miei risultati aneddotici sono che l’esecuzione di questa query su un paio di migliaia di righe completate in meno di un secondo, e in realtà ho avuto 7 sottoquery. Tuttavia, come notato nei commenti, è più costoso da computare farlo in questo modo, quindi fai attenzione nell’usare questo metodo se ti aspetti che funzioni su grandi quantità di dati.

Questo è quello che puoi fare:

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

DEMO

Sto scrivendo uno sp che potrebbe essere utile per questo scopo, in pratica questo sp pivot qualsiasi tabella e restituire una nuova tabella imperniata o restituire solo l’insieme di dati, questo è il modo di eseguirlo:

 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; 

si noti che nel parametro @agg i nomi delle colonne devono essere con '[' e il parametro deve terminare con una virgola ','

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 '[email protected]_table+''; Exec (@query); End; Select @query='Select distinct '[email protected]+' From '+(case when @ind_tmpTbl=1 then 'tempdb.' else '' end)[email protected]+'.'[email protected]+' where '[email protected]+' 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('[email protected]_agg+'(case when '[email protected]+'='+quotename(@pivot_col,char(39))+' then '[email protected]_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 @[email protected][email protected]_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 '[email protected]_cols+','[email protected]_query_pivot+' into '[email protected]_table+' From '+(case when @ind_tmpTbl=1 then 'tempdb.' else '' end)+ @schema+'.'[email protected]+' Group by '[email protected]_cols+';'; print @query; Exec (@query); End; GO 

Questo è un esempio di esecuzione:

 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; 

quindi Select * From ##TEMPORAL1PVT restituirebbe:

inserisci la descrizione dell'immagine qui

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

Ecco una revisione della risposta @Tayrn di cui sopra che potrebbe aiutarti a comprendere un pivoting un po ‘più semplice:

Questo potrebbe non essere il modo migliore per farlo, ma questo è ciò che mi ha aiutato a capire come ruotare i tavoli.

ID = righe che vuoi ruotare

MY_KEY = la colonna che stai selezionando dalla tabella originale che contiene i nomi di colonna che vuoi ruotare.

VAL = il valore che vuoi tornare sotto ogni colonna.

MAX (VAL) => Può essere sostituito con altre funzioni aggregiate. SOMMA (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);