Modo semplice per trasporre colonne e righe in Sql?

Come faccio semplicemente a cambiare le colonne con le righe in SQL? C’è un semplice comando da trasporre?

cioè gira questo risultato:

Paul | John | Tim | Eric Red 1 5 1 3 Green 8 4 3 5 Blue 2 2 9 1 

in questo:

  Red | Green | Blue Paul 1 8 2 John 5 4 2 Tim 1 3 9 Eric 3 5 1 

PIVOT sembra troppo complesso per questo scenario.

Esistono diversi modi per trasformare questi dati. Nel tuo post originale, hai dichiarato che PIVOT sembra troppo complesso per questo scenario, ma può essere applicato molto facilmente utilizzando le funzioni UNPIVOT e PIVOT in SQL Server.

Tuttavia, se non si ha accesso a tali funzioni, è ansible replicare utilizzando UNION ALL su UNPIVOT e quindi una funzione di aggregazione con un’istruzione CASE su PIVOT :

Crea tabella:

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

Versione Union All, Aggregate e 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 

Vedi SQL Fiddle con Demo

L’ UNION ALL esegue l’ UNPIVOT dei dati trasformando le colonne Paul, John, Tim, Eric in righe separate. Quindi si applica la funzione di aggregazione sum() con l’istruzione case per ottenere le nuove colonne per ciascun color .

Versione statica di pivot e pivot:

Sia le funzioni UNPIVOT e PIVOT nel server SQL rendono questa trasformazione molto più semplice. Se si conoscono tutti i valori che si desidera trasformare, è ansible codificarli in una versione statica per ottenere il risultato:

 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 

Vedi SQL Fiddle con Demo

La query interna con UNPIVOT svolge la stessa funzione di UNION ALL . Prende l’elenco di colonne e lo trasforma in righe, quindi il PIVOT esegue la trasformazione finale in colonne.

Versione pivot dynamic:

Se hai un numero sconosciuto di colonne ( Paul, John, Tim, Eric nel tuo esempio) e poi un numero sconosciuto di colors da trasformare puoi usare dynamic sql per generare l’elenco su UNPIVOT e poi su 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) 

Vedi SQL Fiddle con Demo

La versione dynamic interroga sia la tabella sys.columns tabella sys.columns per generare l’elenco di elementi in UNPIVOT e PIVOT . Questo viene quindi aggiunto a una stringa di query per essere eseguita. Il vantaggio della versione dynamic è che se si dispone di un elenco di colors e / o names cambiano, questo genererà l’elenco in fase di esecuzione.

Tutte e tre le query produrranno lo stesso risultato:

 | NAME | RED | GREEN | BLUE | ----------------------------- | Eric | 3 | 5 | 1 | | John | 5 | 4 | 2 | | Paul | 1 | 8 | 2 | | Tim | 1 | 3 | 9 | 

Questo normalmente richiede che tu conosca TUTTE le etichette della riga E della colonna in anticipo. Come puoi vedere nella query sottostante, le etichette sono tutte elencate interamente in entrambe le operazioni UNPIVOT e (ri) PIVOT.

Configurazione schema 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; 

Query 1 :

 select * from tbl unpivot (value for name in ([Paul],[John],[Tim],[Eric])) up pivot (max(value) for color in ([Red],[Green],[Blue])) p 

Risultati :

 | NAME | RED | GREEN | BLUE | ----------------------------- | Eric | 3 | 5 | 1 | | John | 5 | 4 | 2 | | Paul | 1 | 8 | 2 | | Tim | 1 | 3 | 9 | 

Note aggiuntive:

  1. Dato un nome di tabella, è ansible determinare tutti i nomi di colonna da sys.columns o da FOR XML XML usando local-name () .
  2. È inoltre ansible creare l’elenco di colors distinti (o valori per una colonna) utilizzando FOR XML.
  3. Quanto sopra può essere combinato in un batch SQL dinamico per gestire qualsiasi tabella.

Vorrei segnalare qualche altra soluzione per trasporre colonne e righe in SQL.

Il primo è – usando CURSOR. Sebbene il consenso generale nella comunità professionale sia quello di evitare i cursori di SQL Server, esistono ancora casi in cui è consigliabile utilizzare i cursori. Ad ogni modo, i Cursori ci presentano un’altra opzione per trasporre le righe in colonne.

  • Espansione verticale

    Simile al PIVOT, il cursore ha la capacità dynamic di aggiungere più righe mentre il set di dati si espande per includere più numeri di politica.

  • Espansione orizzontale

    A differenza del PIVOT, il cursore eccelle in quest’area in quanto è in grado di espandersi per includere il documento appena aggiunto, senza alterare lo script.

  • Ripartizione delle prestazioni

    La principale limitazione di trasporre le righe in colonne usando CURSOR è uno svantaggio che è collegato all’uso dei cursori in generale – esse hanno un costo significativo delle prestazioni. Questo perché il Cursore genera una query separata per ogni operazione FETCH NEXT.

Un’altra soluzione per trasporre le righe in colonne è usando XML.

La soluzione XML per trasporre le righe in colonne è fondamentalmente una versione ottimale del PIVOT in quanto indirizza la limitazione dynamic delle colonne.

La versione XML dello script risolve questa limitazione utilizzando una combinazione di XML Path, T-SQL dinamico e alcune funzioni integrate (ad esempio STUFF, QUOTENAME).

  • Espansione verticale

    Simile al PIVOT e al Cursore, le nuove politiche aggiunte possono essere recuperate nella versione XML dello script senza alterare lo script originale.

  • Espansione orizzontale

    A differenza del PIVOT, i documenti appena aggiunti possono essere visualizzati senza alterare lo script.

  • Ripartizione delle prestazioni

    In termini di IO, le statistiche della versione XML dello script sono quasi simili a quelle del PIVOT – l’unica differenza è che l’XML ha una seconda scansione della tabella dtTranspose ma questa volta da una cache logica di lettura dei dati.

Puoi trovare ulteriori informazioni su queste soluzioni (compresi alcuni esempi di T-SQL reali) in questo articolo: https://www.sqlshack.com/multiple-options-to-transposing-rows-into-columns/

In base a questa soluzione di bluefeet, ecco una stored procedure che utilizza sql dinamico per generare la tabella trasposta. Richiede che tutti i campi siano numerici ad eccezione della colonna trasposta (la colonna che sarà l’intestazione nella tabella risultante):

 /****** Object: StoredProcedure [dbo].[SQLTranspose] Script Date: 11/10/2015 7:08:02 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Paco Zarate -- Create date: 2015-11-10 -- Description: SQLTranspose dynamically changes a table to show rows as headers. It needs that all the values are numeric except for the field using for transposing. -- Parameters: @TableName - Table to transpose -- @FieldNameTranspose - Column that will be the new headers -- Usage: exec SQLTranspose ,  -- ============================================= ALTER PROCEDURE [dbo].[SQLTranspose] -- Add the parameters for the stored procedure here @TableName NVarchar(MAX) = '', @FieldNameTranspose NVarchar(MAX) = '' AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @colsUnpivot AS NVARCHAR(MAX), @query AS NVARCHAR(MAX), @queryPivot AS NVARCHAR(MAX), @colsPivot as NVARCHAR(MAX), @columnToPivot as NVARCHAR(MAX), @tableToPivot as NVARCHAR(MAX), @colsResult as xml select @tableToPivot = @TableName; select @columnToPivot = @FieldNameTranspose select @colsUnpivot = stuff((select ','+quotename(C.name) from sys.columns as C where C.object_id = object_id(@tableToPivot) and C.name <> @columnToPivot for xml path('')), 1, 1, '') set @queryPivot = 'SELECT @colsResult = (SELECT '','' + quotename('[email protected]+') from '[email protected]+' t where '[email protected]+' <> '''' FOR XML PATH(''''), TYPE)' exec sp_executesql @queryPivot, N'@colsResult xml out', @colsResult out select @colsPivot = STUFF(@colsResult.value('.', 'NVARCHAR(MAX)'),1,1,'') set @query = 'select name, rowid, '[email protected]+' from ( select '[email protected]+' , name, value, ROW_NUMBER() over (partition by '[email protected]+' order by '[email protected]+') as rowid from '[email protected]+' unpivot ( value for name in ('[email protected]+') ) unpiv ) src pivot ( sum(value) for '[email protected]+' in ('[email protected]+') ) piv order by rowid' exec(@query) END

Puoi testarlo con la tabella fornita con questo comando:

 exec SQLTranspose 'yourTable', 'color' 

Sto facendo prima UnPivot e memorizzando i risultati in CTE e utilizzando il CTE in operazione di Pivot .

dimostrazione

 with cte as ( select 'Paul' as Name, color, Paul as Value from yourTable union all select 'John' as Name, color, John as Value from yourTable union all select 'Tim' as Name, color, Tim as Value from yourTable union all select 'Eric' as Name, color, Eric as Value from yourTable ) select Name, [Red], [Green], [Blue] from ( select * from cte ) as src pivot ( max(Value) for color IN ([Red], [Green], [Blue]) ) as Dtpivot; 

Aggiungendo alla magnifica risposta di @Paco Zarate sopra, se vuoi trasporre una tabella che ha più tipi di colonne, aggiungi questa alla fine della riga 39, quindi traspone solo le colonne int :

 and C.system_type_id = 56 --56 = type int 

Ecco la query completa che viene modificata:

 select @colsUnpivot = stuff((select ','+quotename(C.name) from sys.columns as C where C.object_id = object_id(@tableToPivot) and C.name <> @columnToPivot and C.system_type_id = 56 --56 = type int for xml path('')), 1, 1, '') 

Per trovare altri system_type_id , esegui questo:

 select name, system_type_id from sys.types order by name 

Mi piace condividere il codice che sto usando per trasporre un testo splittato basato sulla risposta + bluefeet. In questo approccio sono implementato come una procedura in MS SQL 2005

 SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: ELD. -- Create date: May, 5 2016. -- Description: Transpose from rows to columns the user split function. -- ============================================= CREATE PROCEDURE TransposeSplit @InputToSplit VARCHAR(8000) ,@Delimeter VARCHAR(8000) = ',' AS BEGIN SET NOCOUNT ON; DECLARE @colsUnpivot AS NVARCHAR(MAX) ,@query AS NVARCHAR(MAX) ,@queryPivot AS NVARCHAR(MAX) ,@colsPivot AS NVARCHAR(MAX) ,@columnToPivot AS NVARCHAR(MAX) ,@tableToPivot AS NVARCHAR(MAX) ,@colsResult AS XML SELECT @tableToPivot = '#tempSplitedTable' SELECT @columnToPivot = 'col_number' CREATE TABLE #tempSplitedTable ( col_number INT ,col_value VARCHAR(8000) ) INSERT INTO #tempSplitedTable ( col_number ,col_value ) SELECT ROW_NUMBER() OVER ( ORDER BY ( SELECT 100 ) ) AS RowNumber ,item FROM [DB].[ESCHEME].[fnSplit](@InputToSplit, @Delimeter) SELECT @colsUnpivot = STUFF(( SELECT ',' + quotename(C.NAME) FROM [tempdb].sys.columns AS C WHERE C.object_id = object_id('tempdb..' + @tableToPivot) AND C.NAME <> @columnToPivot FOR XML path('') ), 1, 1, '') SET @queryPivot = 'SELECT @colsResult = (SELECT '','' + quotename(' + @columnToPivot + ') from ' + @tableToPivot + ' t where ' + @columnToPivot + ' <> '''' FOR XML PATH(''''), TYPE)' EXEC sp_executesql @queryPivot ,N'@colsResult xml out' ,@colsResult OUT SELECT @colsPivot = STUFF(@colsResult.value('.', 'NVARCHAR(MAX)'), 1, 1, '') SET @query = 'select name, rowid, ' + @colsPivot + ' from ( select ' + @columnToPivot + ' , name, value, ROW_NUMBER() over (partition by ' + @columnToPivot + ' order by ' + @columnToPivot + ') as rowid from ' + @tableToPivot + ' unpivot ( value for name in (' + @colsUnpivot + ') ) unpiv ) src pivot ( MAX(value) for ' + @columnToPivot + ' in (' + @colsPivot + ') ) piv order by rowid' EXEC (@query) DROP TABLE #tempSplitedTable END GO 

Sto mescolando questa soluzione con le informazioni su come ordinare le file senza ordine da ( SQLAuthority.com ) e la funzione split su MSDN ( social.msdn.microsoft.com )

Quando esegui la prodecure

 DECLARE @RC int DECLARE @InputToSplit varchar(MAX) DECLARE @Delimeter varchar(1) set @InputToSplit = 'hello|beautiful|world' set @Delimeter = '|' EXECUTE @RC = [TransposeSplit] @InputToSplit ,@Delimeter GO 

tu ottieni il prossimo risultato

  name rowid 1 2 3 col_value 1 hello beautiful world