Clever Geek Handbook

Can I use pivot to get counts for each group of values ​​in MSSQL?

I have many groups to group.

UPDATE:
They are in the same column and below is the result "select col from table"

col
12
11
23
22
41
12
37
15
41
23
77
.
.
more
.


I want to get a table like below. 

0~9    10~19    20~29    30~39    40~49    50~59    60~
1      5        4        6        5        2        3


Is this possible from a fulcrum? or I just need to do something like

CASE WHEN COL>=0 AND COL<10 THEN 1 ELSE 0 END AS '0~9'
...

with a bunch of case-whens?

+3


source to share


2 answers


If you want to solve a very strong example THIS you can use:

select [0] [0˜9], [1] [10˜19], [2] [20˜29], [3] [30˜39], [4] [40˜49], [5] [50˜59], [6] [60˜]
from Table1
cross apply (select case when number >= 60 then 6 else cast(number/10 as int) end) N(Decade)
pivot (count(number) for Decade in ([0],[1],[2],[3],[4],[5],[6]))  A

SQL Fiddle

UPDATE



Based on the OP's own answer, the answer that will work for his case is the following:

select [0] [0˜9], [1] [10˜19], [2] [20˜29], [3] [30˜39], [4] [40˜49], [5] [50˜59], [6] [60˜]
from (select number from Table1) O
cross apply (select case when number >= 60 then 6 else cast(number/10 as int) end) N(Decade)
pivot (count(number) for Decade in ([0],[1],[2],[3],[4],[5],[6]))  A

SQL Fiddle

+3


source


This is the result for me.

select [0] [0˜9], [1] [10˜19], [2] [20˜29], [3] [30˜39], [4] [40˜49], [5] [50˜59], [6] [60˜]
from
(select case when number_column >= 60 then 6 else cast(number_column/10 as int) end as decade
from original_table) as source_table
pivot (
count(decade) for decade in ([0],[1],[2],[3],[4],[5],[6])
) as pivot_table

and thanks to Peter Gerkens and Nizam for the tips.

UPDATE:
If there are other columns that are not used for pivoting in the table, the result might look like this.



Nizam of the 1st sentence. Case 1

To resolve this issue, please 1. Nizam updated the proposal. or 2. Below

Case 2

0


source






More articles:


All Articles