Dynamically create columns sql

Question

I have a table of Customers

Customer ID        Name             1               John            2               Lewis            3               Mary          

I have another table CustomerRewards

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

And the final table

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

The customerTypes table is dynamic, many of these types can be added and removed. Basically all I want is the columns to be generated dynamically and a count in each, something like

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 

The problem like I said it that the types are dynamic and the customers are dynamic so I need the columns to be dynamic depending on the types in the system

I have tagged c# as I need this in a DataGridView

Thanks in advance

Answer 1

You will want to use a PIVOT function for this. If you have a known number of columns, then you can hard-code the values:

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; 

See SQL Fiddle with Demo.

Now if you have an unknown number of columns, then you can use dynamic SQL to 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) 

See SQL Fiddle With Demo

If you need to include the Total column, then you can use ROLLUP (Static Version Demo):

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 

Dynamic version (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)