As a corollary to the previous entry in this series, we can take the question further.
When I first heard the requirement:
I need to display the numbers 1-12 in 3 rows of four columns, like so:
1 2 3 4
5 6 7 8
9 10 11 12
my first reaction was “really? Just the numbers 1 to 12? You won’t be given other numbers or values to display in that particular way?”
The answer to that was “no”, so the solution I gave in the last post did the trick.
What if the answer had been “yes – I need to display <some other list of twelve bits of info> in that format”?
Thankfully, we’ve already done most of the work – we know how to display twelve things in 3 rows of 4 columns. The trick with the new requirement is identifying which values go in which place.
We’d do that by artificially labelling the rows with a number from 1 – 12.
Lets take our sample data as:
with sample_data as (select 12 col1 from dual union all
select -1 col1 from dual union all
select 3 col1 from dual union all
select 13 col1 from dual union all
select -20 col1 from dual union all
select 31 col1 from dual union all
select 0 col1 from dual union all
select -9 col1 from dual union all
select -2 col1 from dual union all
select 18 col1 from dual union all
select 7 col1 from dual union all
select 4 col1 from dual)
select col1
from sample_data
order by col1;
which gives us:
COL1
----------
-20
-9
-2
-1
0
3
4
7
12
13
18
31
Reaching into our SQL toolbox, we’ll use the ROW_NUMBER() analytic function to assign a number for each row:
with sample_data as (select 12 col1 from dual union all
select -1 col1 from dual union all
select 3 col1 from dual union all
select 13 col1 from dual union all
select -20 col1 from dual union all
select 31 col1 from dual union all
select 0 col1 from dual union all
select -9 col1 from dual union all
select -2 col1 from dual union all
select 18 col1 from dual union all
select 7 col1 from dual union all
select 4 col1 from dual)
select col1,
row_number() over (order by col1) rn
from sample_data
order by col1;
which gives us:
COL1 RN
---------- ----------
-20 1
-9 2
-2 3
-1 4
0 5
3 6
4 7
7 8
12 9
13 10
18 11
31 12
Aha, now we’re back in familiar territory – we’re back to the numbers 1-12 again!
So, amending the solution from the previous post slightly, we can add it in to give us the results we want:
with sample_data as (select 12 col1 from dual union all
select -1 col1 from dual union all
select 3 col1 from dual union all
select 13 col1 from dual union all
select -20 col1 from dual union all
select 31 col1 from dual union all
select 0 col1 from dual union all
select -9 col1 from dual union all
select -2 col1 from dual union all
select 18 col1 from dual union all
select 7 col1 from dual union all
select 4 col1 from dual),
results as (select col1,
ceil(row_number() over (order by col1)/4) grp,
mod(row_number() over (order by col1), 4) col_pos
from sample_data)
select max(case when col_pos = 1 then col1 end) col1,
max(case when col_pos = 2 then col1 end) col2,
max(case when col_pos = 3 then col1 end) col3,
max(case when col_pos = 0 then col1 end) col4
from results
group by grp
order by grp;
giving us:
COL1 COL2 COL3 COL4
---------- ---------- ---------- ----------
-20 -9 -2 -1
0 3 4 7
12 13 18 31
If you had to adjust the format of the columns – eg. you wanted the resuls in 4 rows of 3 columns, or 6 rows of 2 columns, etc, then you’d have to amend the number you’re using in the ceil and mod functions in the “results” subquery to reflect the number of columns you’re expecting.
Then, in the main query, you’d have to extend or reduce the number of columns accordingly.
I haven’t restricted the number of rows to 12 in my queries above, because my sample data had at most 12 rows. If your requirement was to only display the first (or last!) 12 values of a larger set of data, you’d have to restrict the rows accordingly. I’d simply expose the row_number() as a colum in the “results” subquery and call it something like “rn”, then I’d use “and rn <= 12” in the main query.
Recent Comments