顧客のテーブルがあります
Customer ID Name
1 John
2 Lewis
3 Mary
別のテーブル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テーブルは動的で、これらのタイプの多くは追加したり削除したりできます。基本的に私が欲しいのは、動的に生成される列と、それぞれのカウントです。
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
問題は、私が言ったように、タイプが動的であり、顧客も動的であるため、システム内のタイプに応じて列を動的にする必要があることです。
DataGridViewでこれが必要なので、c#にタグを付けました。
前もって感謝します
ベストアンサー1
これには関数を使用する必要がありますPIVOT
。列の数がわかっている場合は、値をハードコードできます。
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
。
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)
列を含める必要がある場合はTotal
、ROLLUP
(静的バージョンのデモ):
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)