Home » SQL & PL/SQL » SQL & PL/SQL » Column to Row Transpose - Efficient Code Required
Column to Row Transpose - Efficient Code Required [message #188899] Tue, 22 August 2006 05:24 Go to next message
bhagwan
Messages: 86
Registered: September 2004
Member
Hi,

Database:9i

I have below table structure for [B]Table_A[/B] having data.

Column1     Column2     Column3
10          20          30
40          50          60
15          25          35

Desired output is only 1 column.

Result
10
20
30
40
50
60
15
25
35

This can be achieved using UNION clause but since I have more than 20 columns, performance wise it will degrade.

Is there any better solution using analytics functions which will be faster and better?


Regards,
Bhagwan


Re: Column to Row Transpose - Efficient Code Required [message #188906 is a reply to message #188899] Tue, 22 August 2006 06:15 Go to previous messageGo to next message
gbarbisan
Messages: 67
Registered: August 2006
Location: Treviso - Italy
Member
Is the order in the output omportant?
In case not, try (credits to Tom Kyte):

SELECT decode(r, 1, column1, 2, column2, 3, column3) col
  FROM table_a, (SELECT ROWNUM r
                   FROM all_objects
                  WHERE ROWNUM <= 3);
Re: Column to Row Transpose - Efficient Code Required [message #188913 is a reply to message #188899] Tue, 22 August 2006 06:37 Go to previous messageGo to next message
bhagwan
Messages: 86
Registered: September 2004
Member
Hi,

Your solution is good but as you rightly asked, the order is important.

Currently it is showing the result but in random manner.

Rgds,
Bhagwan
Re: Column to Row Transpose - Efficient Code Required [message #188916 is a reply to message #188913] Tue, 22 August 2006 06:50 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What order do you want the results back in?
The example you provide has no obvious order, other than the values from each row appearing sequentially.

Here is how you can keep the results from each row together, but be aware that the rows do not come back in any fixed order.

create table test_order (col_1  varchar2(10), col_2  varchar2(10), col_3 varchar2(10));

insert into test_order values (10,          20,          30);
insert into test_order values (40,          50,          60);
insert into test_order values (15,          25,          35);

SELECT decode(r, 1, col_1, 2, col_2, 3, col_3) col
  FROM (SELECT col_1, col_2, col_3, rownum rnum FROM test_order) t
      ,(SELECT level r
        FROM   dual
        connect by level <= 3)
order by t.rnum;
Re: Column to Row Transpose - Efficient Code Required [message #188921 is a reply to message #188916] Tue, 22 August 2006 07:06 Go to previous messageGo to next message
gbarbisan
Messages: 67
Registered: August 2006
Location: Treviso - Italy
Member
And from what you wrote:

SELECT DECODE(R, 1, COLUMN1, 2, COLUMN2, 3, COLUMN3) COL
  FROM (SELECT COLUMN1, COLUMN2, COLUMN3, ROWNUM RNUM FROM TABLE_A) T,
       (SELECT LEVEL R FROM DUAL CONNECT BY LEVEL <= 3)
 ORDER BY T.RNUM, R;


I modified the order by in:

ORDER BY T.RNUM, R


to obtain the exact output required.
In fact if we:

SELECT *
  FROM (SELECT COLUMN1, COLUMN2, COLUMN3, ROWNUM RNUM FROM TABLE_A) T,
       (SELECT LEVEL R FROM DUAL CONNECT BY LEVEL <= 3)
 ORDER BY T.RNUM, R


we obtain:

   COLUMN1    COLUMN2    COLUMN3       RNUM          R
---------- ---------- ---------- ---------- ----------
        10         20         30          1          1
        10         20         30          1          2
        10         20         30          1          3
        40         50         60          2          1
        40         50         60          2          2
        40         50         60          2          3
        15         25         35          3          1
        15         25         35          3          2
        15         25         35          3          3


while with the original order by:

   COLUMN1    COLUMN2    COLUMN3       RNUM          R
---------- ---------- ---------- ---------- ----------
        10         20         30          1          1
        10         20         30          1          2
        10         20         30          1          3
        40         50         60          2          1
        40         50         60          2          3
        40         50         60          2          2
        15         25         35          3          1
        15         25         35          3          3
        15         25         35          3          2


(note the difference in the last three records).

G.

[Updated on: Tue, 22 August 2006 07:10]

Report message to a moderator

Re: Column to Row Transpose - Efficient Code Required [message #200889 is a reply to message #188899] Wed, 01 November 2006 15:21 Go to previous messageGo to next message
rahul_pardeshi
Messages: 2
Registered: November 2006
Location: Cleveland
Junior Member
I have a table which contains three columns

batch_id, schedule_number, part_positions , component_parent_details
PT1/11/20/43106 18890664 11011 7847980,7847995,7847935,3597936

Now based on the part_positions 11011
11011 implies 12345 but the value for position 3 is 0

so now i have to insert four rows in table another table as

batch_id schedule_number part_number positions
PT1/11/20/43106 18890664 7847980 1
PT1/11/20/43106 18890664 7847995 2
PT1/11/20/43106 18890664 7847935 4
PT1/11/20/43106 18890664 3597936 5


how to do that



Re: Column to Row Transpose - Efficient Code Required [message #200891 is a reply to message #188899] Wed, 01 November 2006 15:24 Go to previous messageGo to next message
rahul_pardeshi
Messages: 2
Registered: November 2006
Location: Cleveland
Junior Member
I have a table which contains three columns

batch_id, ----'PT1/11/20/43106'
schedule_number, ---18890664
part_positions , --11011
component_parent_details '7847980,7847995,7847935,3597936'

Now based on the part_positions 11011
11011 implies 12345 but the value for position 3 is 0

so now i have to insert four rows in table another table as

batch_id schedule_number part_number positions
PT1/11/20/43106 18890664 7847980 1
PT1/11/20/43106 18890664 7847995 2
PT1/11/20/43106 18890664 7847935 4
PT1/11/20/43106 18890664 3597936 5


how to do that



Re: Column to Row Transpose - Efficient Code Required [message #201838 is a reply to message #188899] Tue, 07 November 2006 00:59 Go to previous message
pritika
Messages: 9
Registered: November 2006
Junior Member
The change is marked in dark blue and also it works fine.
SELECT decode(r, 1, col_1, 2, col_2, 3, col_3) col
FROM test_order, (SELECT ROWNUM r
FROM all_objects
WHERE ROWNUM <= 3)
order by col
Previous Topic: how to update a field in an insert trigger
Next Topic: Sample Data model .
Goto Forum:
  


Current Time: Sat Dec 10 06:58:07 CST 2016

Total time taken to generate the page: 0.07115 seconds