Sunday, October 17, 2010

Working around with Transposition of Table data

I was working around with some transposition  and want to share one of the samples .

Consider the customer table ,having customer and month details
customer...... month
Ron................ 1
Kev................. 2
joh................. 1
Nel................. 2
Ave................. 11
Cin................. 10
tra................. 3

Case statement play very important role in transposition of rows to columns and viceversa. In the following scenarios , we can find the extensive usage of case statement 

Scenario 1:
Display total number of customers for each month

jan....feb....mar....apr....may....jun....jul....aug....sep....oct....nov....dec
2......2......1......0......0......0......0......0......0......1......1......0....

The sql query is as follows:

sel
count(case when month = '1' then customer else null end) "jan",
count(case when month = '2' then customer else null end) "feb",
count(case when month = '3' then customer else null end) "mar",
count(case when month = '4' then customer else null end) "apr",
count(case when month = '5' then customer else null end) "may",
count(case when month = '6' then customer else null end) "jun",
count(case when month = '7' then customer else null end) "jul",
count(case when month = '8' then customer else null end) "aug",
count(case when month = '9' then customer else null end) "sep",
count(case when month = '10' then customer else null end) "oct",
count(case when month = '11' then customer else null end) "nov",
count(case when month = '12' then customer else null end) "dec"
from CUST_TABLE ;


Scenario 2:
Display customer and month details with every customer mapped to corresponding month

customer....jan....feb....mar....apr....may....jun....jul....aug....sep....oct....nov....dec
Ron...........1......0......0......0......0......0......0......0......0......0......0......0....
Kev...........0......1......0......0......0......0......0......0......0......0......0......0....
joh...........1......0......0......0......0......0......0......0......0......0......0......0....
Nel...........0......1......0......0......0......0......0......0......0......0......0......0....
Ave...........0......0......0......0......0......0......0......0......0......0......1......0....
Cin...........0......0......0......0......0......0......0......0......0......1......0......0....
Tra...........0......0......1......0......0......0......0......0......0......0......0......0....

The sql query is as follows:

sel
customer,
count(case when month = '1' then customer else null end) "jan",
count(case when month = '2' then customer else null end) "feb",
count(case when month = '3' then customer else null end) "mar",
count(case when month = '4' then customer else null end) "apr",
count(case when month = '5' then customer else null end) "may",
count(case when month = '6' then customer else null end) "jun",
count(case when month = '7' then customer else null end) "jul",
count(case when month = '8' then customer else null end) "aug",
count(case when month = '9' then customer else null end) "sep",
count(case when month = '10' then customer else null end) "oct",
count(case when month = '11' then customer else null end) "nov",
count(case when month = '12' then customer else null end) "dec"
from CUST_TABLE;

1 comment:

  1. An alternative approach to this would be:

    Jan |Feb
    =============
    Ron |Kev
    Joh |Nel

    SELECT
    CASE WHEN month = '1' THEN CUSTOMER ELSE NULL END AS "Jan",
    CASE WHEN month = '2' THEN CUSTOMER ELSE NULL END AS "Feb"
    FROM cust_table;

    ReplyDelete