How to Divide Sql Column

Question

I want to share the SQL table column. The first column will contain data upto 1-10, 11-20 in the second column and 21-30 in the 3rd column in the form of data.

please share the image directly instead of hyperlink – Muhammad Waheed Mar 11 '19 at 06:50 — Muhammad Waheed, Mar 11 '19 at 06:50

Fahmi · Answer 1 · 2019-03-11T07:07:55.157

2

use conditional aggregation with case when expression

select max(case when pagenumber<11 then pagenumber end) as "1-10",
max(case when pagenumber<21 and pagenumber>10 then pagenumber end) as "11-20",
max(case when pagenumber<31 and pagenumber>20 then pagenumber end) as "21-30"
from tablename

edited Mar 11 '19 at 07:07

answered Mar 11 '19 at 06:52

Fahmi

37,315
5
22
31

3

It's just a small thing, but I've always thought it good practice to use " for quoting column names, per the SQL ISO standard - https://stackoverflow.com/questions/2901453/sql-standard-to-escape-column-names – Caius Jard Mar 11 '19 at 07:06
@CaiusJard thank u bro - i'll definitely keep it mind :) – Fahmi Mar 11 '19 at 07:07
The first column will be 1-10, then in the column 11-20, however each will come in accordance with the serial, as such 1 2 3 4 5 ... – Md. Altaf Hossain Mar 11 '19 at 11:40
@fa06; @caius-jard; 1-10 11-20 21-30 value 10 20 30 – Md. Altaf Hossain Mar 11 '19 at 12:12

score 0 · Accepted Answer · answered Mar 12 '19 at 10:23

I think fa06's answer is missing a necessary group by

select  
    max(case when (pagenumber-1)/10 = 0 then pagenumber end) as "1-10",
    max(case when (pagenumber-1)/10 = 1 then pagenumber end) as "11-20",
    max(case when (pagenumber-1)/10 = 2 then pagenumber end) as "21-30"
from t
group by (pagenumber%10)
order by 1

I did the breakdown mathematically, but you could also do something like:

select  
    max(case when pagenumber between 1 and 10 then pagenumber end) as "1-10",
    max(case when pagenumber between 11 and 20 then pagenumber end) as "11-20",
    max(case when pagenumber between 21 and 30 pagenumber end) as "21-30"
from t
group by (pagenumber%10)
order by 1

The group by puts (for example) 1, 11 and 21 on the same row, because they all have the same result to the mod operation: 1. 2, 12, 22 go on another row (mod result is 2).

If your requirement is for e.g. 1, 12 and 23 to all go on the same row, you'll have to get more complex with your grouping (find a way to make 1, 12 and 23 mathematically all return the same value.. like (x%10)-((x/10)+1) )

How to Divide Sql Column

2 Answers2