动态创建列sql [英] Dynamically create columns sql
问题描述
我有一张客户表
Customer ID Name
1 John
2 Lewis
3 Mary
我还有另一个表CustomerRewards
I have another table CustomerRewards
TypeID Description
1 Bronze
2 Silver
3 Gold
4 Platinum
5 AnotherOne
决赛桌
RewardID TypeID CustomerID
1 1 1
2 1 1
3 2 1
4 2 2
customerTypes表是动态的,可以添加和删除许多这些类型.基本上,我想要的是动态生成的列以及每个列的计数,例如
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
我已经在DataGridView中标记了c#,因为我需要它
I have tagged c# as I need this in a DataGridView
预先感谢
推荐答案
您将为此使用PIVOT
函数.如果您知道已知的列数,则可以对值进行硬编码:
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;
请参见带有演示的SQL小提琴.
现在,如果列数未知,则可以使用动态SQL来PIVOT
:
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)
请参见带有演示的SQL小提琴
如果您需要包含Total
列,则可以使用ROLLUP
(静态版本演示):
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
动态版本(演示):
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)
这篇关于动态创建列sql的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!