SQL Server: trasporre le righe sulle colonne

A parte scrivere il cursore leggendo ogni riga e popolarlo in colonne, qualsiasi altra alternativa se devo trasporre ogni riga in colonne?

TimeSeconds TagID Value 1378700244 A1 3.75 1378700245 A1 30 1378700304 A1 1.2 1378700305 A2 56 1378700344 A2 11 1378700345 A3 0.53 1378700364 A1 4 1378700365 A1 14.5 1378700384 A1 144 1378700384 A4 10 

Il numero di colonne non è fisso.

Output: ho appena assegnato n / a come segnaposto per nessun dato in quell’intersezione.

 TimeSec A1 A2 A3 A4 1378700244 3.75 n/an/an/a 1378700245 30 n/an/an/a 1378700304 1.2 n/an/an/a 1378700305 n/a 56 n/an/a 1378700344 n/a 11 n/an/a 1378700345 n/an/a 0.53 n/a 1378700364 n/an/an/a 4 1378700365 14.5 n/an/an/a 1378700384 144 n/an/a 10 

Spero tu possa condividere con me alcuni suggerimenti. Grazie.

Un modo per farlo se i valori tagID sono noti in anticipo consiste nell’utilizzare l’aggregazione condizionale

 SELECT TimeSeconds, COALESCE(MAX(CASE WHEN TagID = 'A1' THEN Value END), 'n/a') A1, COALESCE(MAX(CASE WHEN TagID = 'A2' THEN Value END), 'n/a') A2, COALESCE(MAX(CASE WHEN TagID = 'A3' THEN Value END), 'n/a') A3, COALESCE(MAX(CASE WHEN TagID = 'A4' THEN Value END), 'n/a') A4 FROM table1 GROUP BY TimeSeconds 

o se stai bene con valori NULL invece di 'n/a'

 SELECT TimeSeconds, MAX(CASE WHEN TagID = 'A1' THEN Value END) A1, MAX(CASE WHEN TagID = 'A2' THEN Value END) A2, MAX(CASE WHEN TagID = 'A3' THEN Value END) A3, MAX(CASE WHEN TagID = 'A4' THEN Value END) A4 FROM table1 GROUP BY TimeSeconds 

o con PIVOT

 SELECT TimeSeconds, A1, A2, A3, A4 FROM ( SELECT TimeSeconds, TagID, Value FROM table1 ) s PIVOT ( MAX(Value) FOR TagID IN (A1, A2, A3, A4) ) p 

Uscita (con NULL s):

 TimeSeconds A1 A2 A3 A4
 ----------- ------- ------ ----- -----
 1378700244 3.75 NULL NULL NULL
 1378700245 30,00 NULL NULL NULL
 1378700304 1.20 NULL NULL NULL
 1378700305 NULL 56,00 NULL NULL
 1378700344 NULL 11,00 NULL NULL
 1378700345 NULL NULL 0,53 NULL
 1378700364 4,00 NULL NULL NULL
 1378700365 14,50 NULL NULL NULL
 1378700384 144,00 NULL NULL 10.00

Se devi calcolare i valori TagID in modo dinamico, utilizza SQL dinamico

 DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX) SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(TagID) FROM Table1 ORDER BY 1 FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)'),1,1,'') SET @sql = 'SELECT TimeSeconds, ' + @cols + ' FROM ( SELECT TimeSeconds, TagID, Value FROM table1 ) s PIVOT ( MAX(Value) FOR TagID IN (' + @cols + ') ) p' EXECUTE(@sql) 

SQL Server ha un comando PIVOT che potrebbe essere quello che stai cercando.

 select * from Tag pivot (MAX(Value) for TagID in ([A1],[A2],[A3],[A4])) as TagTime; 

Se le colonne non sono costanti, dovrai combinare questo con un SQL dinamico.

 DECLARE @columns AS VARCHAR(MAX); DECLARE @sql AS VARCHAR(MAX); select @columns = substring((Select DISTINCT ',' + QUOTENAME(TagID) FROM Tag FOR XML PATH ('')),2, 1000); SELECT @sql = 'SELECT * FROM TAG PIVOT ( MAX(Value) FOR TagID IN( ' + @columns + ' )) as TagTime;'; execute(@sql); 

Un’altra opzione che potrebbe essere adatta in questa situazione è l’utilizzo di XML

L’opzione 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 alla 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 ,  -- table and FIeldToTranspose should be written using single quotes -- ============================================= 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

Avevo un requisito leggermente diverso, per cui dovevo trasporre in modo selettivo le colonne in righe.

Il tavolo aveva colonne:

 create table tbl (ID, PreviousX, PreviousY, CurrentX, CurrentY) 

Avevo bisogno di colonne per Previous e Current e righe per X e Y Un prodotto cartesiano generato su una tabella statica ha funzionato bene, ad esempio:

 select ID, max(case when metric='X' then PreviousX case when metric='Y' then PreviousY end) as Previous, max(case when metric='X' then CurrentX case when metric='Y' then CurrentY end) as Current from tbl inner join /* Cartesian product - transpose by repeating row and picking appropriate metric column for period */ ( VALUES (1, 'X'), (2, 'Y')) AS x (sort, metric) ON 1=1 group by ID order by ID, sort