Obtener FILAS como COLUMNAS (consulta PIVOT dinámica de SQL Server)

Estate atento ya que en este enunciado encontrarás la solución que buscas.Este post fue aprobado por nuestros expertos para garantizar la calidad y veracidad de nuestro contenido.

Solución:

Lo que estás tratando de hacer se llama PIVOT. Hay dos formas de hacer esto, ya sea con un pivote estático o un pivote dinámico.

Pivote estático: es donde codificará los valores de las filas para transformarlos 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 las columnas se determinan 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 incorporado PIVOT funcionalidad. Sin embargo, con frecuencia encuentro la PIVOT y UNPIVOT la nomenclatura es confusa y todavía tengo que encontrar 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 quiere hacer un pivote dinámico, solo necesita construir un string de las 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, '[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)

Calificaciones y reseñas

Nos puedes amparar nuestro quehacer fijando un comentario o dejando una puntuación te estamos eternamente agradecidos.

---

---

Utiliza Nuestro Buscador