Transpose columns and rows

Sql

Public Domain

Transpose code from long format to wide format.

Download (right click, save as, rename as appropriate)

Embed

       

Tags:

oracle sql transpose 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
--- example1 table has the following structure: -----------------
create table EXAMPLE1
(
  PROFILEID  NUMBER(10) not null,
  MONTHFIELD VARCHAR2(6),
  COUNTHOURS NUMBER
)


-- Step 1 generate the decode rows for the table
select 'sum(decode(monthfield,'''|| v||''',counthours,0)) C'||v||',' from
(select distinct monthfield v from EXAMPLE1 order by v);


-- Step 2 paste the generated code below
select profileid, 
-- paste here :
sum(decode(monthfield,'200407',counthours,0)) C200407,
sum(decode(monthfield,'200408',counthours,0)) C200408,
sum(decode(monthfield,'200507',counthours,0)) C200507,
sum(decode(monthfield,'200508',counthours,0)) C200508,
sum(decode(monthfield,'200509',counthours,0)) C200509,
sum(decode(monthfield,'200510',counthours,0)) C200510,
sum(decode(monthfield,'200511',counthours,0)) C200511,
sum(decode(monthfield,'200512',counthours,0)) C200512,
sum(decode(monthfield,'200601',counthours,0)) C200601,
sum(decode(monthfield,'200602',counthours,0)) C200602,
sum(decode(monthfield,'200603',counthours,0)) C200603,
sum(decode(monthfield,'200604',counthours,0)) C200604,
sum(decode(monthfield,'200605',counthours,0)) C200605,
sum(decode(monthfield,'200606',counthours,0)) C200606,
sum(decode(monthfield,'200607',counthours,0)) C200607,
sum(decode(monthfield,'200608',counthours,0)) C200608,
sum(decode(monthfield,'200609',counthours,0)) C200609,
sum(decode(monthfield,'200610',counthours,0)) C200610,
sum(decode(monthfield,'200611',counthours,0)) C200611,
sum(decode(monthfield,'200612',counthours,0)) C200612,
sum(decode(monthfield,'200701',counthours,0)) C200701
-- end paste
from example1 t
group by profileid ;