Rows to Columns

Distributing Top to Bottom - 1

This topic demonstrates how to collect and spread values from multiple rows under a single column, like those in VAL below

GROUP_KEY  VAL
---------- ----------
G4         a
G4         b
G4         c
G4         d

G7         a
G7         b
G7         c
G7         d
G7         e
G7         f
G7         g
 

across multiple columns, rows, and groups like this.

GROUP_KEY  COL_0      COL_1      COL_2
---------- ---------- ---------- ----------
G4         a          c          d
G4         b

G7         a          d          f
G7         b          e          g
G7         c
 

Unlike prior topics in this section, here we have a base table with no PIVOT_KEY column. Values will be distributed evenly from top to bottom, then left to right across an arbitrary number of columns (this is sometimes referred to as "newspaper column" style). Empty cells will be distributed evenly as well.

11g PIVOT Solution

break on group_key duplicates skip 1

select
  group_key ,
  col_0 ,
  col_1 ,
  col_2
from
( select
    group_key ,
    pivot_key ,
    row_number() over( partition by group_key, pivot_key order by val )
      as row_num ,
    val
  from
  ( select
      group_key ,
      ntile(3) over ( partition by group_key order by val ) - 1
        as pivot_key ,
      val
    from t5
  ) t5a
) t5b pivot
    ( max(val)
      for pivot_key in
        (
          0 as col_0 ,
          1 as col_1 ,
          2 as col_2
        )
    )
order by group_key, row_num ;
 
GROUP_KEY  COL_0      COL_1      COL_2
---------- ---------- ---------- ----------
G0

G1         a

G2         a          b

G3         a          b          c

G4         a          c          d
G4         b

G5         a          c          e
G5         b          d

G6         a          c          e
G6         b          d          f

G7         a          d          f
G7         b          e          g
G7         c

G8         a          d          g
G8         b          e          h
G8         c          f
 

With two columns instead of three the query would look like this.

select
  group_key ,
  col_0 ,
  col_1
  -- this line deleted
from
( select
    group_key ,
    pivot_key ,
    row_number() over( partition by group_key, pivot_key order by val )
      as row_num ,
    val
  from
  ( select
      group_key ,
      ntile(2) over ( partition by group_key order by val ) - 1 -- use "2" here
        as pivot_key ,
      val
    from t5
  ) t5a
) t5b pivot
    ( max(val)
      for pivot_key in
        (
          0 as col_0 ,
          1 as col_1
          -- this line deleted
        )
    )
order by group_key, row_num ;
 
GROUP_KEY  COL_0      COL_1
---------- ---------- ----------
G0

G1         a

G2         a          b

G3         a          c
G3         b

G4         a          c
G4         b          d

G5         a          d
G5         b          e
G5         c

G6         a          d
G6         b          e
G6         c          f

G7         a          e
G7         b          f
G7         c          g
G7         d

G8         a          e
G8         b          f
G8         c          g
G8         d          h
 

The sort order can be controlled by changing the ORDER BY clauses in the analytic function arguments.

select
  group_key ,
  col_0 ,
  col_1 ,
  col_2
from
( select
    group_key ,
    pivot_key ,
    row_number() over( partition by group_key, pivot_key ORDER BY VAL DESC)
      as row_num ,
    val
  from
  ( select
      group_key ,
      ntile(3) over ( partition by group_key ORDER BY VAL DESC) - 1
        as pivot_key ,
      val
    from t5
  ) t5a
) t5b pivot
    ( max(val)
      for pivot_key in
        (
          0 as col_0 ,
          1 as col_1 ,
          2 as col_2
        )
    )
order by group_key, row_num ;
 
GROUP_KEY  COL_0      COL_1      COL_2
---------- ---------- ---------- ----------
G0

G1         a

G2         b          a

G3         c          b          a

G4         d          b          a
G4         c

G5         e          c          a
G5         d          b

G6         f          d          b
G6         e          c          a

G7         g          d          b
G7         f          c          a
G7         e

G8         h          e          b
G8         g          d          a
G8         f          c
 

How it Works

The ROW_NUM and PIVOT_KEY values created by the inline views are the key to this solution.

break on pivot_key duplicates skip 1

select
  group_key ,
  pivot_key ,
  row_number() over( partition by group_key, pivot_key order by val )
    as row_num ,
  val
from
( select
    group_key ,
    ntile(3) over ( partition by group_key order by val ) - 1
      as pivot_key ,
    val
  from t5
  where group_key = 'G8'
) t5a
order by group_key, pivot_key, row_num ;
 
GROUP_KEY   PIVOT_KEY    ROW_NUM VAL
---------- ---------- ---------- ----------
G8                  0          1 a
G8                  0          2 b
G8                  0          3 c

G8                  1          1 d
G8                  1          2 e
G8                  1          3 f

G8                  2          1 g
G8                  2          2 h
 

Once the PIVOT_KEY and ROW_NUM columns have been created, VAL values are simply distributed and collapsed using the PIVOT clause the same way they were in the Transposing M:1 topic.

Earlier Versions

For those who are not working with Oracle 11g the following query works in Oracle versions from 8i onward.

break on group_key duplicates skip 1

select
  group_key ,
  max( decode( pivot_key, 0, val, null ) ) col_0 ,
  max( decode( pivot_key, 1, val, null ) ) col_1 ,
  max( decode( pivot_key, 2, val, null ) ) col_2
from
( select
    group_key ,
    pivot_key ,
    row_number() over( partition by group_key, pivot_key order by val )
      as row_num ,
    val
  from
  ( select
      group_key ,
      ntile(3) over ( partition by group_key order by val ) - 1
        as pivot_key ,
      val
    from t5
  ) t5a
) t5b
group by group_key, row_num
order by group_key, row_num ;
 
GROUP_KEY  COL_0      COL_1      COL_2
---------- ---------- ---------- ----------
G0

G1         a

G2         a          b

G3         a          b          c

G4         a          c          d
G4         b

G5         a          c          e
G5         b          d

G6         a          c          e
G6         b          d          f

G7         a          d          f
G7         b          e          g
G7         c

G8         a          d          g
G8         b          e          h
G8         c          f
 



Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=http://www.sqlsnippets.com/en/topic-12194.html]SQL Snippets: Rows to Columns - Distributing Top to Bottom - 1[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="http://www.sqlsnippets.com/en/topic-12194.html">SQL Snippets: Rows to Columns - Distributing Top to Bottom - 1</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: Rows to Columns - Distributing Top to Bottom - 1
  • URL (href): http://www.sqlsnippets.com/en/topic-12194.html