modificar - Crear dinámicamente columnas sql

sql server select columna calculada

Question

Tengo una tabla de Clientes

Customer ID        Name         
   1               John        
   2               Lewis        
   3               Mary         

Tengo otra mesa CustomerRewards

 TypeID           Description
   1                Bronze
   2                Silver
   3                Gold
   4               Platinum
   5               AnotherOne

Y la mesa final

 RewardID          TypeID          CustomerID
    1                1                 1
    2                1                 1
    3                2                 1
    4                2                 2

La tabla customerTypes es dinámica, muchos de estos tipos se pueden agregar y eliminar. Básicamente, todo lo que quiero es que las columnas se generen dinámicamente y cuenten en cada una, algo así como

CustomerName        Bronze        Silver     Gold      Platinum     AnotherOne    total
   John               2             1         0           0             0           3
   Lewis              0             1         0           0             0           1
 Grand TOTAL          2             2         0           0             0           4

El problema es que dije que los tipos son dinámicos y que los clientes son dinámicos, por lo que necesito que las columnas sean dinámicas según los tipos del sistema.

He etiquetado c # como necesito esto en un DataGridView

Gracias por adelantado

Answer 1

PIVOT usar una función PIVOT para esto. Si tiene un número conocido de columnas, puede codificar los valores:

select name, [Bronze], [Silver], [Gold], [Platinum], [AnotherOne]
from
(
  select c.name,
    cr.description,
    r.typeid
  from customers c
  left join rewards r
    on c.id = r.customerid
  left join customerrewards cr
    on r.typeid = cr.typeid
) x
pivot
(
  count(typeid)
  for description in ([Bronze], [Silver], [Gold], [Platinum], [AnotherOne])
) p;

Ver SQL Fiddle con demostración .

Ahora, si tiene un número desconocido de columnas, puede usar SQL dinámico para PIVOT :

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT '','' + QUOTENAME(description) 
                    from customerrewards
                    group by description, typeid
                    order by typeid
            FOR XML PATH(''''), TYPE
            ).value(''.'', ''NVARCHAR(MAX)'') 
        ,1,1,'''')

set @query = ''SELECT name,'' + @cols + '' from 
             (
                select c.name,
                  cr.description,
                  r.typeid
                from customers c
                left join rewards r
                  on c.id = r.customerid
                left join customerrewards cr
                  on r.typeid = cr.typeid
            ) x
            pivot 
            (
                count(typeid)
                for description in ('' + @cols + '')
            ) p ''

execute(@query)

Ver SQL Fiddle con demostración

Si necesita incluir la columna Total , entonces puede usar ROLLUP ( Demo de versión estática ):

select name, sum([Bronze]) Bronze, sum([Silver]) Silver, 
  sum([Gold]) Gold, sum([Platinum]) Platinum, sum([AnotherOne]) AnotherOne
from 
(
  select name, [Bronze], [Silver], [Gold], [Platinum], [AnotherOne]
  from
  (
    select c.name,
      cr.description,
      r.typeid
    from customers c
    left join rewards r
      on c.id = r.customerid
    left join customerrewards cr
      on r.typeid = cr.typeid
  ) x
  pivot
  (
    count(typeid)
    for description in ([Bronze], [Silver], [Gold], [Platinum], [AnotherOne])
  ) p
) x
group by name with rollup

Versión dinámica ( Demo ):

DECLARE @cols AS NVARCHAR(MAX),
    @colsRollup AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT '','' + QUOTENAME(description) 
                    from customerrewards
                    group by description, typeid
                    order by typeid
            FOR XML PATH(''''), TYPE
            ).value(''.'', ''NVARCHAR(MAX)'') 
        ,1,1,'''')

select @colsRollup 
      = STUFF((SELECT '', Sum('' + QUOTENAME(description) + '') as ''+ QUOTENAME(description)
                    from customerrewards
                    group by description, typeid
                    order by typeid
            FOR XML PATH(''''), TYPE
            ).value(''.'', ''NVARCHAR(MAX)'') 
        ,1,1,'''')


set @query 
          = ''SELECT name, ''+ @colsRollup + ''
             FROM
             (
                SELECT name,'' + @cols + '' from 
                 (
                    select c.name,
                      cr.description,
                      r.typeid
                    from customers c
                    left join rewards r
                      on c.id = r.customerid
                    left join customerrewards cr
                      on r.typeid = cr.typeid
                ) x
                pivot 
                (
                    count(typeid)
                    for description in ('' + @cols + '')
                ) p 
              ) x1
              GROUP BY name with ROLLUP''

execute(@query)