Dynamically create columns sql
dynamic-pivot, pivot, sql, sql-server, t-sql
Solution
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)
Problem
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