Rows to Columns

This section presents techniques for transforming rows into columns. Three different types of transformations are covered - aggregating, transposing, and distributing.

Aggregating

Aggregating refers to transformations that take values like these

GROUP_KEY  PIVOT_KEY         VAL
---------- ---------- ----------
G0         P0                 10
G0         P1                  5
G0         P1                  3
G0         P2                 20
G0         P2                  6

G1         P1                  9
G1         P1         (null)
G1         P2         (null)
G1         P2                  5

G2         (null)              4
G2         (null)              7
G2         (null)             10

(null)     P0                 15
(null)     P2         (null)
(null)     (null)     (null)
 

and aggregate them into a crosstab matrix like this.

GROUP_KEY      P0_SUM     P1_SUM     P2_SUM UNASSIGNED_SUM
---------- ---------- ---------- ---------- --------------
G0                 10          8         26
G1                             9          5
G2                                                      21
(null)             15
 

This transformation is commonly referred to as "pivoting" or creating a "crosstab" report.

Transposing

Transposing refers to transformations that take values like these

GROUP_KEY  PIVOT_KEY  VAL
---------- ---------- ----------
G0         P0         a
G0         P1         A
G0         P2         1
G0         (null)     xyz

G1         P0         b
G1         P1         B
G1         P2         (null)

(null)     P0         c
(null)     P2         3
(null)     (null)     (null)
 

and rearrange them into a matrix like this.

GROUP_KEY  P0         P1         P2         UNASSIGNED
---------- ---------- ---------- ---------- ----------
G0         a          A          1          xyz
G1         b          B
(null)     c                     3
 

This transformation is also commonly referred to as "pivoting" or creating a "crosstab" report.

Distributing

Distributing refers to transformations that take values like these

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

and rearrange them over an arbitrary number of columns like as in result, which spreads values across three columns ordered from left to right

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

or this result, which spreads values across two columns ordered from top to bottom (aka "newspaper column style").

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

Tags

Crosstab Pivot Query
Crosstab Query Pivot Table
Pivot Print Rows Horizontally
Pivoting Print Rows Across Instead of Down
Rotate Rows



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-12180.html]SQL Snippets: SQL Techniques Tutorials - Rows to Columns[/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-12180.html">SQL Snippets: SQL Techniques Tutorials - Rows to Columns</a>

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

  • Link Text : SQL Snippets: SQL Techniques Tutorials - Rows to Columns
  • URL (href): http://www.sqlsnippets.com/en/topic-12180.html

Revision Notes

Date Category Note
2007-12-02 Updated Topic The SQL Snippets "Rows to Columns" tutorial has been updated to include solutions that use the new 11g PIVOT clause of the SELECT command.