Ottieni ROWS come COLUMNS (query PIVOT dynamic di SQL Server)

Sto usando MS SQL 2008 R2, ho tre tabelle con lo schema seguente:

Tabella 1: contiene informazioni sul workshift per ciascun lavoratore

CREATE TABLE workshift ( [ws_id] [bigint] NOT NULL, [start_date] [datetime] NOT NULL, [end_date] [datetime] NOT NULL, [worker_id] [bigint] NOT NULL ) INSERT INTO workshift VALUES (1, '2012-08-20 08:30:00', '2012-08-20 14:30:00', 1) INSERT INTO workshift VALUES (2, '2012-08-20 14:30:00', '2012-08-20 22:30:00', 2) 

Tabella 2: contiene denominazioni monetarie

 CREATE TABLE currency_denom ( [cd_id] [decimal](7, 2) NOT NULL, [name] [nchar](100) NOT NULL ) INSERT INTO currency_denom VALUES (1, '100.00') INSERT INTO currency_denom VALUES (2, '50.00') INSERT INTO currency_denom VALUES (3, '20.00') INSERT INTO currency_denom VALUES (4, '10.00') INSERT INTO currency_denom VALUES (5, '5.00') INSERT INTO currency_denom VALUES (6, '1.00') 

Tabella 3: contiene la quantità di ciascuna denominazione che il lavoratore ha ricevuto in ogni turno di lavoro

 CREATE TABLE currency_by_workshift ( [cd_id] [decimal](7, 2) NOT NULL, [ws_id] [bigint] NOT NULL, [qty] [int] NOT NULL ) INSERT INTO currency_by_workshift VALUES (1, 1, 1) INSERT INTO currency_by_workshift VALUES (2, 1, 2) INSERT INTO currency_by_workshift VALUES (3, 1, 2) INSERT INTO currency_by_workshift VALUES (2, 2, 3) INSERT INTO currency_by_workshift VALUES (4, 2, 4) INSERT INTO currency_by_workshift VALUES (5, 2, 2) 

Ho bisogno di ottenere i valori currency_by_workshift nelle colonne anziché nelle righe, insieme ai valori del turno di lavoro, ovvero:

 workshift | workshift | workshift | 100.00 | 50.00 | 20.00 | 10.00 | 5.00 | 1.00 ws_id | start_date | end_date | | | | | | 1 | 2012-08-20 08:30:00 | 2012-08-20 14:30:00 | 1 | 2 | 2 | 0 | 0 | 0 2 | 2012-08-20 14:30:00 | 2012-08-20 22:30:00 | 0 | 2 | 0 | 4 | 2 | 0 

Non sono in grado di utilizzare un caso per contare le quantità per ogni denominazione di valuta perché sono configurabili, se viene aggiunta una nuova denominazione, la query deve essere modificata. Lo stesso vale se si utilizza la funzione PIVOT o mi sbaglio?

Come posso ottenere le informazioni in questo modo?

Quello che stai cercando di fare è chiamato PIVOT . Ci sono due modi per farlo, con un pivot statico o un pivot dinamico.

Static Pivot – è dove dovrai codificare i valori delle righe da trasformare in colonne (vedi SQL Fiddle con Demo ):

 select ws_id, start_date, end_date, IsNull([100.00], 0) [100.00], IsNull([50.00], 0) [50.00], IsNull([20.00], 0) [20.00], IsNull([10.00], 0) [10.00], IsNull([5.00], 0) [5.00], IsNull([1.00], 0) [1.00] from ( select ws.ws_id, ws.start_date, ws.end_date, cd.name, cbw.qty from workshift ws left join currency_by_workshift cbw on ws.ws_id = cbw.ws_id left join currency_denom cd on cbw.cd_id = cd.cd_id ) x pivot ( sum(qty) for name in ([100.00], [50.00], [20.00], [10.00], [5.00], [1.00]) ) p 

Il pivot dinamico è dove le colonne sono determinate in fase di esecuzione (vedi SQL Fiddle con Demo ):

 DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX), @colsPivot AS NVARCHAR(MAX) select @colsPivot = STUFF((SELECT ', IsNull(' + QUOTENAME(rtrim(name)) +', 0) as ['+ rtrim(name)+']' from currency_denom GROUP BY name ORDER BY cast(name as decimal(10, 2)) desc FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') select @cols = STUFF((SELECT distinct ', ' + QUOTENAME(name) from currency_denom FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = 'SELECT ws_id, start_date, end_date,' + @colsPivot + ' from ( select ws.ws_id, ws.start_date, ws.end_date, cd.name, cbw.qty from workshift ws left join currency_by_workshift cbw on ws.ws_id = cbw.ws_id left join currency_denom cd on cbw.cd_id = cd.cd_id ) x pivot ( sum(qty) for name in (' + @cols + ') ) p ' execute(@query) 

Entrambe le versioni produrranno gli stessi risultati.

@ bluefeet ha fornito un’ottima risposta utilizzando la funzionalità PIVOT integrata. Tuttavia, trovo spesso confuso la nomenclatura PIVOT e UNPIVOT e devo ancora incontrare una situazione in cui gli stessi risultati non possono essere raggiunti con aggregazioni standard:

 select w.ws_id, w.start_date, w.end_date, [100.00] = isnull(sum(case when c.name='100.00' then cw.qty else null end), 0), [50.00] = isnull(sum(case when c.name='50.00' then cw.qty else null end), 0), [20.00] = isnull(sum(case when c.name='20.00' then cw.qty else null end), 0), [10.00] = isnull(sum(case when c.name='10.00' then cw.qty else null end), 0), [5.00] = isnull(sum(case when c.name='5.00' then cw.qty else null end), 0), [1.00] = isnull(sum(case when c.name='1.00' then cw.qty else null end), 0) from workshift w join currency_by_workshift cw on w.ws_id=cw.ws_id join currency_denom c on cw.cd_id=c.cd_id group by w.ws_id, w.start_date, w.end_date 

Se vuoi fare un pivot dinamico, devi solo creare una stringa delle colonne pivot una volta:

 DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) select @cols = stuff(( select replace(',[@name] = isnull(sum(case when c.name=''@name'' then cw.qty else null end), 0)' , '@name', rtrim(name)) from currency_denom order by cd_id for xml path(''), type ).value('.', 'nvarchar(max)') ,1,1,'') select @query = ' select w.ws_id, w.start_date, w.end_date, '+@cols+' from workshift w join currency_by_workshift cw on w.ws_id=cw.ws_id join currency_denom c on cw.cd_id=c.cd_id group by w.ws_id, w.start_date, w.end_date ' execute(@query)