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)