Obter ROWS como COLUMNS (consulta PIVOT dinâmica do SQL Server)

Estou usando o MS SQL 2008 R2, tenho três tabelas com o seguinte esquema:

Tabela 1: contém informações do turno de trabalho para cada trabalhador

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) 

Tabela 2: Contém denominações monetárias

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

Tabela 3: Contém a quantidade de cada denominação que o trabalhador recebeu em cada turno de trabalho

 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) 

Eu preciso para obter os valores de Currency_by_workshift em colunas em vez de linhas, juntamente com os valores do turno de trabalho, ou seja:

 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 

Eu não sou capaz de usar um caso para contar quantidades para cada denominação de moeda, porque eles são configuráveis, se uma nova denominação é adicionada, a consulta deve ser modificada. O mesmo se aplica se estiver usando a function PIVOT ou estou errado?

Como posso obter as informações dessa maneira?

O que você está tentando fazer é chamado de PIVOT . Existem duas maneiras de fazer isso, seja com um Pivô Estático ou um Dinâmico.

Static Pivot – é onde você codificará os valores das linhas para transformar em colunas (consulte SQL Fiddle with 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 

O pivô dynamic é onde as colunas são determinadas em tempo de execução (consulte SQL Fiddle with 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) 

Ambas as versões produzirão os mesmos resultados.

O @bluefeet forneceu uma resposta muito boa utilizando a funcionalidade integrada do PIVOT . No entanto, eu freqüentemente acho a nomenclatura PIVOT e UNPIVOT confusa e ainda não encontrei uma situação onde os mesmos resultados não podem ser alcançados com agregações padrão:

 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 você quiser fazer um pivô dynamic, você só precisa construir uma string das colunas pivô uma vez:

 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)