Home » SQL & PL/SQL » SQL & PL/SQL » Rearrange column values
Rearrange column values [message #388062] Mon, 23 February 2009 01:39 Go to next message
adchax
Messages: 32
Registered: June 2008
Member
Hi
I was a cursor that creates the following set of data

a b
2289 18287
1880 2289
22249 10880
19114 22249
18287 19114

i need to store them in a zig zag manner with column a in column b1 and column b in column a1,with exception of the first a column(2289) that goes in the last of b1
the relation is

b1-->a2
b2-->a3
b3-->a4
b4-->a5
b5-->a1

so the result required is:

a1 b1
18287 10880
2289 22249
10880 19114
22249 18287
19114 2289

i am getting values from a coursor and need to insert it in a table type

Thanks

[MERGED by LF]

[Updated on: Mon, 23 February 2009 03:27] by Moderator

Report message to a moderator

Re: Rearrange column values [message #388066 is a reply to message #388062] Mon, 23 February 2009 01:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ There is no order in a table so you have to specify one
2/ ROWNUM will give you the number of the row you are
3/ MOD will give you if you are in odd or even row
4/ DECODE/CASE will then help you to know if you have to display A or B column

Post a Test case: create table and insert statements along with the result you want with these data.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Start with this and post what you find, we will then study the special of the first value in last row (remember you have to specify an order and post a test case).

Regards
Michel

[Updated on: Mon, 23 February 2009 01:47]

Report message to a moderator

Rearrange column data [message #388088 is a reply to message #388062] Mon, 23 February 2009 02:59 Go to previous messageGo to next message
adchax
Messages: 32
Registered: June 2008
Member
I have a cursor that creates data like the following
A B
2289 18287
1880 2289
22249 10880
19114 22249
18287 19114
I need to store the data in teh following zig zag format of(considering C and D are the new columns of the new table)
b1-->c1
a2-->d1
b2-->c2
a3-->d2
b3-->c3
a4-->d3
b4-->c4
a5-->d4
b5-->c5
a1-->d5 data is placed in the opposite column position
except the a1 which is placed the lowest position of the opposite column



C D
18287 10880
2289 22249
10880 19114
22249 18287
19114 2289

How do i create the following data to be inserted into a table type


Thanks

[Updated on: Mon, 23 February 2009 03:03]

Report message to a moderator

Re: Rearrange column data [message #388089 is a reply to message #388088] Mon, 23 February 2009 03:12 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
A much simpler way of describing your request would be to say that given this data:
 A      B
 2289  18287
 1880  2289
 22249 10880
 19114 22249
 18287 19114

You want to select the A and the previous rows value of B

It's slightly trickier that that, in that you want to wrap the end values round - ie the first row gets the last value of B.

Look into the analytic functions LAG and LAST_VALUE
Re: Rearrange column data [message #388091 is a reply to message #388089] Mon, 23 February 2009 03:21 Go to previous messageGo to next message
adchax
Messages: 32
Registered: June 2008
Member
I have posted an example of how the data should be arranged.
Smile
  • Attachment: text.JPG
    (Size: 14.19KB, Downloaded 67 times)
Re: Rearrange column data [message #388097 is a reply to message #388091] Mon, 23 February 2009 03:29 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
@adchax,

as you have chosen not to accept private messages, here it is, in public:

Please, do not multipost.

You have opened two topics which deal with the same problem. I have merged them. Next time, continue discussion in the original one.
Re: Rearrange column data [message #388099 is a reply to message #388097] Mon, 23 February 2009 03:33 Go to previous messageGo to next message
adchax
Messages: 32
Registered: June 2008
Member
thanks..i tried to delete the other but couldnt do it!
Re: Rearrange column data [message #388106 is a reply to message #388091] Mon, 23 February 2009 04:11 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
adchax wrote on Mon, 23 February 2009 10:21
I have posted an example of how the data should be arranged.
Smile

Michel Cadot wrote on Mon, 23 February 2009 08:47
Post a Test case: create table and insert statements along with the result you want with these data.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).
Regards
Michel

Previous Topic: Query regarding order by??
Next Topic: sql query
Goto Forum:
  


Current Time: Sun Dec 04 04:33:58 CST 2016

Total time taken to generate the page: 0.06408 seconds