SQL Pivot Tips -  Links within this page

SQL Pivot Tips - Outside Links

Technique to pivot a table from horizontal to vertical orientation using SQL?
Technique to pivot a table from vertical to horizontal orientation using SQL?

Link to a “one title line and one detail line” pivot technique  
Link to a simple pivoting technique, well explained

Tylogix - SQL Pivot Tips and Techniques

On how to pivot a table from horizontal to vertical orientation using SQL :

Method:

Here is an easy to follow technique. First, create a one-column table to contain a single digit (1 to 12) per month:

Create table month_numeric
 (
   Month_Value  integer
 ) ;

insert into month_numeric values (1);
insert into month_numeric values (2);
insert into month_numeric values (3);
insert into month_numeric values (4);
insert into month_numeric values (5);
insert into month_numeric values (6);
insert into month_numeric values (7);
insert into month_numeric values (8);
insert into month_numeric values (9);
insert into month_numeric values (10);
insert into month_numeric values (11);
insert into month_numeric values (12);

The idea is to use a CROSS JOIN, which will yield a combination of all 12 months for each row in the table to be pivoted and then use a CASE statement to pick the right value depending on the month processed for each new vertical row.

INSERT INTO VERTICAL
 (
  YEAR        ,
  ACCOUNT_ID  ,
  MONTH       ,
  NET_POSTING
 )

SELECT
  YEAR              ,
  MONTH             ,
  ACCOUNT_ID        ,
  CASE MONTH_VALUE
       WHEN 1  THEN NET_01
       WHEN 2  THEN NET_02
       WHEN 3  THEN NET_03
       WHEN 4  THEN NET_04
       WHEN 5  THEN NET_05
       WHEN 6  THEN NET_06
       WHEN 7  THEN NET_07
       WHEN 8  THEN NET_08
       WHEN 9  THEN NET_09
       WHEN 10 THEN NET_10
       WHEN 11 THEN NET_11
       WHEN 12 THEN NET_12
  END
FROM   HORIZONTAL
CROSS JOIN  MONTH_NUMERIC ;

Another example that can be used, using the Relative Record Number (equivalent of Row ID in Oracle), is

Go Back to the Top

On how to pivot a table from vertical to horizontall orientation using SQL :

Method:

This technique is a bit more involved than the previous one, but it works equally well. The idea is to join the table to itself for as many columns as there are to produce horizontally:

INSERT INTO HORIZONTAL
( 
   YEAR        ,
   ACCOUNT_ID  ,
   NET_01      ,
   NET_02      ,
   NET_03      ,
   NET_04      ,
   NET_05      ,
   NET_06      ,
   NET_07      ,
   NET_08      ,
   NET_09      ,
   NET_10      ,
   NET_11      ,
   NET_12      ,
)

SELECT 
 V01.YEAR                     ,
 V01.ACCOUNT_ID               ,
 V01.NET_POSTING              ,
 IFNULL(V02.NET_POSTING , 0)  ,
 IFNULL(V03.NET_POSTING , 0)  ,
 IFNULL(V04.NET_POSTING , 0)  ,
 IFNULL(V05.NET_POSTING , 0)  ,
 IFNULL(V06.NET_POSTING , 0)  ,
 IFNULL(V07.NET_POSTING , 0)  ,
 IFNULL(V08.NET_POSTING , 0)  ,
 IFNULL(V09.NET_POSTING , 0)  ,
 IFNULL(V10.NET_POSTING , 0)  ,
 IFNULL(V11.NET_POSTING , 0)  ,
 IFNULL(V12.NET_POSTING , 0) 

FROM VERTICAL V01   
LEFT OUTER JOIN VERTICAL V02     
on   V01. YEAR        = V02. YEAR       
and  V01. ACCOUNT_ID  = V02. ACCOUNT_ID 

LEFT OUTER JOIN VERTICAL V03     
on   V01. YEAR        = V03. YEAR       
and  V01. ACCOUNT_ID  = V03. ACCOUNT_ID 

LEFT OUTER JOIN VERTICAL V04     
on   V01. YEAR   = V04. YEAR       
and  V01. ACCOUNT_ID  = V04. ACCOUNT_ID 

LEFT OUTER JOIN VERTICAL V05     
on   V01. YEAR   = V05. YEAR       
and  V01. ACCOUNT_ID  = V05. ACCOUNT_ID 

LEFT OUTER JOIN VERTICAL V06     
on   V01. YEAR   = V06. YEAR       
and  V01. ACCOUNT_ID  = V06. ACCOUNT_ID 

LEFT OUTER JOIN VERTICAL V07     
on   V01. YEAR   = V07. YEAR       
and  V01. ACCOUNT_ID  = V07. ACCOUNT_ID 

LEFT OUTER JOIN VERTICAL V08     
on   V01. YEAR   = V08. YEAR       
and  V01. ACCOUNT_ID  = V08. ACCOUNT_ID 

LEFT OUTER JOIN VERTICAL V09     
on   V01. YEAR   = V09.YEAR       
and  V01. ACCOUNT_ID  = V09.ACCOUNT_ID

LEFT OUTER JOIN VERTICAL V10     
on   V01. YEAR   = V10.YEAR       
and  V01. ACCOUNT_ID  = V10.ACCOUNT_ID 

LEFT OUTER JOIN VERTICAL V11     
on   V01. YEAR   = V11. YEAR       
and  V01. ACCOUNT_ID  = V11. ACCOUNT_ID 

LEFT OUTER JOIN VERTICAL V12     
on   V01. YEAR   = V12. YEAR       
and  V01. ACCOUNT_ID  = V12. ACCOUNT_ID

WHERE    V01.MONTH_VALUE = 01        
     and V02.MONTH_VALUE = 02     
     and V03.MONTH_VALUE = 03     
     and V04.MONTH_VALUE = 04     
     and V05.MONTH_VALUE = 05     
     and V06.MONTH_VALUE = 06     
     and V02.MONTH_VALUE = 07     
     and V03.MONTH_VALUE = 08     
     and V04.MONTH_VALUE = 09     
     and V05.MONTH_VALUE = 10     
     and V06.MONTH_VALUE = 11     
     and V06.MONTH_VALUE = 12 
;

Go Back to the Top