HomeUncategorized › Writing “complex” SQL – a how to, pt1b

Writing “complex” SQL – a how to, pt1b

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.

6 Comments.[ Leave a comment ]

  1. Starting with 11G, one could use PIVOT if desired. Using the same WITH clauses, the main SELECT would be

    select col1, col2, col3, col4 from results
    pivot(max(col1) for col_pos in(1 col1,2 col2,3 col3,0 col4))
    order by grp;

    • Sorry, didn’t see the previous post where you said “can’t use PIVOT”.

      • Yeah, but it’s good that you posted the PIVOT way of doing it – thanks!

        • If you do not want max (you are grouping nothing, it is a misleading use of max), you could try with lead, lag, first_value, nth_value, last_value.

          select a,b,c,d from (select
          row_number() over (order by col1) rn,
          col1 a,
          lead(col1,1) over (order by col1) b,
          lead(col1,2) over (order by col1) c,
          lead(col1,3) over (order by col1) d
          from sample_data) where mod(rn,4)=1

  2. another technique for spreadsheeting that do not use aggregation is model :


    SELECT a,b,c,d FROM sample_data
    MODEL
    return updated rows
    DIMENSION BY (ROW_NUMBER () OVER (ORDER BY col1) rn)
    MEASURES (col1, to_number(null) a, to_number(null) b, to_number(null) c, to_number(null) d, count(*) over () cc)
    rules (
    a[for rn from 1 to (cc[1]+3)/4 increment 1]=col1[cv()*4-3],
    b[for rn from 1 to (cc[1]+3)/4 increment 1]=col1[cv()*4-2],
    c[for rn from 1 to (cc[1]+3)/4 increment 1]=col1[cv()*4-1],
    d[for rn from 1 to (cc[1]+3)/4 increment 1]=col1[cv()*4]
    );

    it is however not a syntax that I would use, as hardly anybody in my company will be able to read this !

  3. Just for fun :

    select
    listagg(lpad(col1,6),' ') within group (order by col1) " COL1 COL2 COL3 COL4"
    from sample_data
    group by trunc((rownum-1)/4)

    COL1 COL2 COL3 COL4
    ---------------------------
    -1 3 12 13
    -20 -9 0 31
    -2 4 7 18

Leave a Comment

NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>