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.
-
2please share the image directly instead of hyperlinkMuhammad Waheed– Muhammad Waheed2019-03-11 06:50:11 +00:00Commented Mar 11, 2019 at 6:50
Add a comment
|
2 Answers
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
4 Comments
Caius Jard
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 - stackoverflow.com/questions/2901453/…
Fahmi
@CaiusJard thank u bro - i'll definitely keep it mind :)
Md. Altaf Hossain
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
@fa06; @caius-jard; 1-10 11-20 21-30 value 10 20 30
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) )