How to get a summary table like this? A pivot table?

Question

I am using T-SQL with MS SQL Server.

I have a table like this:

Category         Value
A                 150
B                 200
C                 300
A                 120
A                 300
C                 500
D                 200
...

I want to get a summary table like this:

DistinctCategory     Value>100    Value>200    Value>300 ...(column value step is 100)
A                      3              1            0
B                      1              0            0
C                      2              2            1
D                      1              0            0
...

I guess a SQL pivot table can do this, but how to implement it?

Ideally, I want the summary table can be self-adaptive to the value range in the original table, and make the value step as 100 for each column. Can it be done with T-SQL script?

And more ideally, I hope I can specify the start/end/step value. :)

possible duplicate of [Dynamic Pivot Columns in SQL Server](http://stackoverflow.com/questions/14797691/dynamic-pivot-columns-in-sql-server) — Kyle Hale, Apr 25 '14 at 14:48

Hamidreza · Answer 1 · 2014-04-19T06:48:14.823

You can do something like this:

with
tab1 as (select 100 as v1,(select max(value) from table1) v2,table1.* from table1),
tab2(v1,v2,category,value) as
(select v1,v2,category,value from tab1 
where value > v1
union all
select v1 + 100 as v1,v2,category,value from tab2 
where value > v1 + 100 and v2 >= v1),
tab3 as (select v1,category,count(*) cnt from tab2
group by v1,category)
select * from
(select * from tab3) as sour
PIVOT
(
    MAX([cnt])
    FOR [v1] IN ([100],[200],[300],[400])
) as pvt;

or this:

with
tab1 as (select 100 as v1,(select max(value) from table1) v2,table1.* from table1),
tab2(v1,v2,category,value) as
(select v1,v2,category,value from tab1 
where value > v1
union all
select v1 + 100 as v1,v2,category,value from tab2 
where value > v1 + 100 and v2 >= v1),
tab3 as (select v1,category,count(*) cnt from tab2
group by v1,category)
select category,
MAX(CASE WHEN v1 = 100 THEN cnt ELSE NULL END) [value > 100],
MAX(CASE WHEN v1 = 200 THEN cnt ELSE NULL END) [value > 200],
MAX(CASE WHEN v1 = 300 THEN cnt ELSE NULL END) [value > 300],
MAX(CASE WHEN v1 = 400 THEN cnt ELSE NULL END) [value > 400]
from tab3 group by category;

SQL Fiddle

Thanks. Is it possible not to hardcode the start/end/step value? And in the pivot part, is it possible not to list all the unique values because that depends on the start/end/step value. — smwikipedia, Apr 19 '14 at 06:40

score 0 · Answer 2 · answered Apr 19 '14 at 07:52

You can do something like this query if you want to do all the things dynamically:

DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)

with
tab1 as (select 100 as v1,(select max(value) from table1) v2,table1.* from table1),
tab2(v1,v2,category,value) as
(select v1,v2,category,value from tab1 
where value > v1
union all
select v1 + 100 as v1,v2,category,value from tab2 
where value > v1 + 100 and v2 >= v1),
tab3 as (select v1,category,count(*) cnt from tab2
group by v1,category)--,
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.v1) 
        FROM tab3 c
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

set @query =
'with
tab1 as (select 100 as v1,(select max(value) from table1) v2,table1.* from table1),
tab2(v1,v2,category,value) as
(select v1,v2,category,value from tab1 
where value > v1
union all
select v1 + 100 as v1,v2,category,value from tab2 
where value > v1 + 100 and v2 >= v1),
tab3 as (select v1,category,count(*) cnt from tab2
group by v1,category)
select * from
(select * from tab3) as sour
PIVOT
(
    MAX([cnt])
    FOR [v1] IN ('+ @cols +')
) as pvt'

execute(@query);

How to get a summary table like this? A pivot table?

2 Answers2

SQL Fiddle

SQL Fiddle