Solución:
Lo que intentas hacer se llama PIVOT
. Hay dos formas de hacer esto, ya sea con un pivote estático o un pivote dinámico.
Static Pivot: es donde codificará los valores de las filas para transformarlas en columnas (consulte 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 dinámico es donde se determinan las columnas en tiempo de ejecución (consulte 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 proporcionó una muy buena respuesta utilizando el PIVOT
funcionalidad. Sin embargo, con frecuencia encuentro PIVOT
y UNPIVOT
nomenclatura confusa y todavía no he encontrado una situación en la que no se puedan lograr los mismos resultados 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 desea hacer un pivote dinámico, solo necesita crear una cadena de columnas dinámicas 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, '[email protected]+'
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)