Obtenga ROWS como COLUMNS (consulta dinámica PIVOT de SQL Server)

Estoy usando MS SQL 2008 R2, tengo tres tablas con el siguiente esquema:

Tabla 1: contiene información de turno de trabajo para cada trabajador

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) 

Tabla 2: contiene denominaciones monetarias

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

Tabla 3: contiene la cantidad de cada denominación que el trabajador ha recibido en cada turno laboral

 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) 

Necesito obtener los valores de currency_by_workshift en columnas en lugar de filas, junto con los valores de workshift, es decir:

 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 

No puedo usar un caso para contar cantidades para cada denominación de moneda porque son configurables, si se agrega una nueva denominación, la consulta debe modificarse. Lo mismo aplica si se usa la función PIVOT, o estoy equivocado?

¿Cómo puedo obtener la información de esa manera?

Lo que estás tratando de hacer se llama PIVOT . Hay dos formas de hacerlo, ya sea con un Pivote estático o un Pivote dynamic.

Píxel estático: es donde codificará los valores de las filas para transformarlas en columnas (Ver SQL Fiddle con demostración ):

 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 

El pivote dynamic es donde se determinan las columnas en tiempo de ejecución (ver SQL Fiddle con demostración ):

 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 versiones producirán los mismos resultados.

@bluefeet brindó una muy buena respuesta utilizando la funcionalidad PIVOT incorporada. Sin embargo, con frecuencia encuentro que la nomenclatura UNPIVOT y UNPIVOT confusa y aún no he encontrado una situación donde los mismos resultados no se puedan lograr con agregaciones estándar:

 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 

Si quiere hacer un pivote dynamic, solo necesita construir una cadena de columnas pivote una 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)