Converting Columns into Rows.

[anjali.sud]

I have a requirement where I need to convert rows in columns. The data is quite huge there would be around 200 million records. The sample table contains only data for five months but in actual 12 months of data come in a single row and number of columns are also much larger than this.

Database is - Oracle 10g.

Example –
Input Table Output Table
A_SK A_SK
B_SK B_SK
C_SK C_SK
D_SK D_SK
E_SK E_SK
T1_SK T_SK
T2_SK N
T3_SK MZ
T4_SK
T5_SK
N1
N2
N3
N4
N5
M1
M2
M3
M4
M5

Input Data
A_SK B_SK C_SK D_SK E_SK T1_SK T2_SK T3_SK T4_SK T5_SK N1 N2 N3 N4 N5 M1 M2 M3 M4 M5
101 200 300 400 500 501 502 503 504 505 1 4 45 45 44 43 42 41 42 78
100 200 300 400 500 501 502 503 504 505 1.2 0 1.3 11 11 114 1.7 1.9 202 1.78
102 200 300 400 500 501 502 503 504 505 5 6 7 7 8 9 9 44 3 3

Output Data

A_SK B_SK C_SK D_SK E_SK T_SK N MZ
101 200 300 400 500 501 1 43
101 200 300 400 500 502 4 42
101 200 300 400 500 503 45 41
101 200 300 400 500 504 45 42
101 200 300 400 500 505 44 78
102 200 300 400 500 501 1.2 114
102 200 300 400 500 502 0 1.7
102 200 300 400 500 503 1.3 1.9
102 200 300 400 500 504 11 202
102 200 300 400 500 505 11 1.78
100 200 300 400 500 501 5 9
100 200 300 400 500 502 6 9
100 200 300 400 500 503 7 44
100 200 300 400 500 504 7 3
100 200 300 400 500 505 8 3


Is it feasible to achieve this using Oracle MODEL clause. I tried using MODEL clause but its seams there is constraint that columns can not be directly used in the rules. Could you please advice on this.

select a_sk, b_sk, c_sk, d_sk, e_sk, t_sk, n, mz
from test_model
model
partition by (a_sk,b_sk,c_sk,d_sk,e_sk)
dimension by (0 as t_sk)
measures (0 as n,0 as mz)
unique dimension
rules upsert
(
n[t1_sk] = n1,
n[t2_sk] = n2,
n[t3_sk] = n3,
n[t4_sk] = n4,
n[t5_sk] = n5,
mz[t1_sk] = m1,
mz[t2_sk] = m2,
mz[t3_sk] = m3,
mz[t4_sk] = m4,
mz[t5_sk] = m5,
t_sk[t1_sk] = 501,
t_sk[t2_sk] = 502,
t_sk[t3_sk] = 503,
t_sk[t4_sk] = 504,
t_sk[t5_sk] = 505
)