Rows to Columns

Distributing Left to Right

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

GROUP_KEY  VAL
---------- ----------
G8         a
G8         b
G8         c
G8         d
G8         e
G8         f
G8         g
G8         h
 

across multiple columns, rows, and groups like this.

GROUP_KEY  COL_0      COL_1      COL_2
---------- ---------- ---------- ----------
G8         a          b          c
G8         d          e          f
G8         g          h
 

Unlike prior topics in this section here we will deal with a scenario where there is no PIVOT_KEY column in the base table. Values will be distributed evenly from left to right, then top to bottom across an arbitrary number of columns.

11g PIVOT Solution

break on group_key duplicates skip 1

column group_key null '(null)'
column val       null '(null)'

select
  group_key ,
  col_0 ,
  col_1 ,
  col_2
from
  ( select
      group_key,
      mod( row_number() over ( partition by group_key order by val ) - 1, 3 )
        as pivot_key ,
      trunc
        ( ( row_number() over ( partition by group_key order by val ) - 1 ) / 3 )
        as row_num ,
      val
    from t5
  ) t5a 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          b          c
G4         d

G5         a          b          c
G5         d          e

G6         a          b          c
G6         d          e          f

G7         a          b          c
G7         d          e          f
G7         g

G8         a          b          c
G8         d          e          f
G8         g          h
 

To spread values across 5 columns instead of 3 the query would look like this.

select
  group_key ,
  col_0 ,
  col_1 ,
  col_2 ,
  col_3 , -- add this line
  col_4   -- add this line
from
  ( select
      group_key,
      mod( row_number() over ( partition by group_key order by val ) - 1, 5 )     -- use "5" here
        as pivot_key ,
      trunc
        ( ( row_number() over ( partition by group_key order by val ) - 1 ) / 5 ) -- use "5" here
        as row_num ,
      val
    from t5
  ) t5a pivot
    ( max(val)
      for pivot_key in
        (
          0 as col_0 ,
          1 as col_1 ,
          2 as col_2 ,
          3 as col_3 , -- add this line
          4 as col_4   -- add this line
        )
    )
order by group_key, row_num ;
 
GROUP_KEY  COL_0      COL_1      COL_2      COL_3      COL_4
---------- ---------- ---------- ---------- ---------- ----------
G0

G1         a

G2         a          b

G3         a          b          c

G4         a          b          c          d

G5         a          b          c          d          e

G6         a          b          c          d          e
G6         f

G7         a          b          c          d          e
G7         f          g

G8         a          b          c          d          e
G8         f          g          h
 

How it Works

The solution works basically the same as the one presented in Transposing M:1. The only differences are with the inline view, which produces values like these.

break on pivot_key duplicates skip 1

select
  group_key,
  mod( row_number() over ( partition by group_key order by val ) - 1, 3 )
    as pivot_key ,
  trunc
    ( ( row_number() over ( partition by group_key order by val ) - 1 ) / 3 )
    as row_num ,
  val
from t5
where group_key = 'G8'
order by group_key, pivot_key, row_num ;
 
GROUP_KEY   PIVOT_KEY    ROW_NUM VAL
---------- ---------- ---------- ----------
G8                  0          0 a
G8                  0          1 d
G8                  0          2 g

G8                  1          0 b
G8                  1          1 e
G8                  1          2 h

G8                  2          0 c
G8                  2          1 f
 

The key feature in the inline view is the PIVOT_KEY column created using MOD( ROW_NUMBER() .... This column contains an auto generated column number for each VAL value. This allows us to later place the VAL value in the appropriate column using the PIVOT ... IN clause.

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,
      mod( row_number() over ( partition by group_key order by val ) - 1, 3 )
        as pivot_key ,
      trunc
        ( ( row_number() over ( partition by group_key order by val ) - 1 ) / 3 )
        as row_num ,
      val
    from t5
  ) t5a
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          b          c
G4         d

G5         a          b          c
G5         d          e

G6         a          b          c
G6         d          e          f

G7         a          b          c
G7         d          e          f
G7         g

G8         a          b          c
G8         d          e          f
G8         g          h
 



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-12196.html]SQL Snippets: Rows to Columns - Distributing Left to Right[/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-12196.html">SQL Snippets: Rows to Columns - Distributing Left to Right</a>

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

  • Link Text : SQL Snippets: Rows to Columns - Distributing Left to Right
  • URL (href): http://www.sqlsnippets.com/en/topic-12196.html