Colums To Rows

[CHUMPY]

I have a table I have inherited similar in structure to the following

Columns
ID Varchar2(20)
lower_timevalue 1 NUMBER
lower_timevalue 2 NUMBER
........
lower_timevalue 24 NUMBER
middle_timevalue 1 NUMBER
middle_timevalue 2 NUMBER
........
middle_timevalue 24 NUMBER
upper_timevalue 1 NUMBER
upper_timevalue 2 NUMBER
........
upper_timevalue 24 NUMBER


I would like to convert the table to the following format

ID VARCHAR2(20)
time DATE
upper NUMBER
lower NUMBER
middle NUMBER

I have come across this sort of problem in the past and can't remember the elegant solution to move all the records in a relatively easy transition!

If any body can point me in the right direction I would be very grateful!

Gareth

 

[sem]

You need 24 unions

Regards, Dima

 

[CHUMPY]

Thanks.... I think I know what you mean but can't quite get it to wrk any chance of a little example?

Thankyou in advance..

Gareth

 

[SantaMufasa]

Gareth, One question: Where is "Time" coming from? Otherwise, here is your code:

create table FirstNormalFormProblems
(ID Varchar2(20)
,lower_timevalue_1 NUMBER
,lower_timevalue_2 NUMBER
,lower_timevalue_3 NUMBER
,lower_timevalue_4 NUMBER
,lower_timevalue_5 NUMBER
,lower_timevalue_6 NUMBER
,lower_timevalue_7 NUMBER
,lower_timevalue_8 NUMBER
,lower_timevalue_9 NUMBER
,lower_timevalue_10 NUMBER
,lower_timevalue_11 NUMBER
,lower_timevalue_12 NUMBER
,lower_timevalue_13 NUMBER
,lower_timevalue_14 NUMBER
,lower_timevalue_15 NUMBER
,lower_timevalue_16 NUMBER
,lower_timevalue_17 NUMBER
,lower_timevalue_18 NUMBER
,lower_timevalue_19 NUMBER
,lower_timevalue_20 NUMBER
,lower_timevalue_21 NUMBER
,lower_timevalue_22 NUMBER
,lower_timevalue_23 NUMBER
,lower_timevalue_24 NUMBER
,middle_timevalue_1 NUMBER
,middle_timevalue_2 NUMBER
,middle_timevalue_3 NUMBER
,middle_timevalue_4 NUMBER
,middle_timevalue_5 NUMBER
,middle_timevalue_6 NUMBER
,middle_timevalue_7 NUMBER
,middle_timevalue_8 NUMBER
,middle_timevalue_9 NUMBER
,middle_timevalue_10 NUMBER
,middle_timevalue_11 NUMBER
,middle_timevalue_12 NUMBER
,middle_timevalue_13 NUMBER
,middle_timevalue_14 NUMBER
,middle_timevalue_15 NUMBER
,middle_timevalue_16 NUMBER
,middle_timevalue_17 NUMBER
,middle_timevalue_18 NUMBER
,middle_timevalue_19 NUMBER
,middle_timevalue_20 NUMBER
,middle_timevalue_21 NUMBER
,middle_timevalue_22 NUMBER
,middle_timevalue_23 NUMBER
,middle_timevalue_24 NUMBER
,upper_timevalue_1 NUMBER
,upper_timevalue_2 NUMBER
,upper_timevalue_3 NUMBER
,upper_timevalue_4 NUMBER
,upper_timevalue_5 NUMBER
,upper_timevalue_6 NUMBER
,upper_timevalue_7 NUMBER
,upper_timevalue_8 NUMBER
,upper_timevalue_9 NUMBER
,upper_timevalue_10 NUMBER
,upper_timevalue_11 NUMBER
,upper_timevalue_12 NUMBER
,upper_timevalue_13 NUMBER
,upper_timevalue_14 NUMBER
,upper_timevalue_15 NUMBER
,upper_timevalue_16 NUMBER
,upper_timevalue_17 NUMBER
,upper_timevalue_18 NUMBER
,upper_timevalue_19 NUMBER
,upper_timevalue_20 NUMBER
,upper_timevalue_21 NUMBER
,upper_timevalue_22 NUMBER
,upper_timevalue_23 NUMBER
,upper_timevalue_24 NUMBER
);
insert into FirstNormalFormProblems values
(1
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24
,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54
,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84
);
insert into FirstNormalFormProblems values
(2
,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124
,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154
,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184
);
drop table LessFirstNormalProblems
/
create table LessFirstNormalProblems
(ID VARCHAR2(20)
,time DATE
,upper NUMBER
,lower NUMBER
,middle NUMBER
);
insert into LessFirstNormalProblems
select id,null,lower_timevalue_1,middle_timevalue_1,upper_timevalue_1
from FirstNormalFormProblems
union all
select id,null,lower_timevalue_2,middle_timevalue_2,upper_timevalue_2
from FirstNormalFormProblems
union all
select id,null,lower_timevalue_3,middle_timevalue_3,upper_timevalue_3
from FirstNormalFormProblems
union all
select id,null,lower_timevalue_4,middle_timevalue_4,upper_timevalue_4
from FirstNormalFormProblems
union all
select id,null,lower_timevalue_5,middle_timevalue_5,upper_timevalue_5
from FirstNormalFormProblems
union all
select id,null,lower_timevalue_6,middle_timevalue_6,upper_timevalue_6
from FirstNormalFormProblems
union all
select id,null,lower_timevalue_7,middle_timevalue_7,upper_timevalue_7
from FirstNormalFormProblems
union all
select id,null,lower_timevalue_8,middle_timevalue_8,upper_timevalue_8
from FirstNormalFormProblems
union all
select id,null,lower_timevalue_9,middle_timevalue_9,upper_timevalue_9
from FirstNormalFormProblems
union all
select id,null,lower_timevalue_10,middle_timevalue_10,upper_timevalue_10
from FirstNormalFormProblems
union all
select id,null,lower_timevalue_11,middle_timevalue_11,upper_timevalue_11
from FirstNormalFormProblems
union all
select id,null,lower_timevalue_12,middle_timevalue_12,upper_timevalue_12
from FirstNormalFormProblems
union all
select id,null,lower_timevalue_13,middle_timevalue_13,upper_timevalue_13
from FirstNormalFormProblems
union all
select id,null,lower_timevalue_14,middle_timevalue_14,upper_timevalue_14
from FirstNormalFormProblems
union all
select id,null,lower_timevalue_15,middle_timevalue_15,upper_timevalue_15
from FirstNormalFormProblems
union all
select id,null,lower_timevalue_16,middle_timevalue_16,upper_timevalue_16
from FirstNormalFormProblems
union all
select id,null,lower_timevalue_17,middle_timevalue_17,upper_timevalue_17
from FirstNormalFormProblems
union all
select id,null,lower_timevalue_18,middle_timevalue_18,upper_timevalue_18
from FirstNormalFormProblems
union all
select id,null,lower_timevalue_19,middle_timevalue_19,upper_timevalue_19
from FirstNormalFormProblems
union all
select id,null,lower_timevalue_20,middle_timevalue_20,upper_timevalue_20
from FirstNormalFormProblems
union all
select id,null,lower_timevalue_21,middle_timevalue_21,upper_timevalue_21
from FirstNormalFormProblems
union all
select id,null,lower_timevalue_22,middle_timevalue_22,upper_timevalue_22
from FirstNormalFormProblems
union all
select id,null,lower_timevalue_23,middle_timevalue_23,upper_timevalue_23
from FirstNormalFormProblems
union all
select id,null,lower_timevalue_24,middle_timevalue_24,upper_timevalue_24
from FirstNormalFormProblems
/
col id format a2
col lower format 99999
col upper format 99999
col middle format 999999
select * from LessFirstNormalProblems order by id;
ID TIME UPPER LOWER MIDDLE
-- --------- ------ ------ -------
1 1 31 61
1 2 32 62
1 4 34 64
1 6 36 66
1 8 38 68
1 10 40 70
1 12 42 72
1 14 44 74
1 16 46 76
1 24 54 84
1 23 53 83
1 22 52 82
1 21 51 81
1 20 50 80
1 19 49 79
1 18 48 78
1 17 47 77
1 15 45 75
1 13 43 73
1 11 41 71
1 9 39 69
1 7 37 67
1 5 35 65
1 3 33 63
2 101 131 161
2 124 154 184
2 123 153 183
2 122 152 182
2 121 151 181
2 120 150 180
2 119 149 179
2 118 148 178
2 117 147 177
2 116 146 176
2 115 145 175
2 114 144 174
2 113 143 173
2 112 142 172
2 111 141 171
2 110 140 170
2 109 139 169
2 102 132 162
2 104 134 164
2 106 136 166
2 108 138 168
2 107 137 167
2 105 135 165
2 103 133 163

 

[CHUMPY]

Thankyou very much for a detailed example.

Regards


Gareth

 

[sem]

Dave, do you have a Perl script to generate such queries? I mean, even Gareth have not risked to provide complete table structure

Regards, Dima