Home » SQL & PL/SQL » SQL & PL/SQL » single row with multiple column into single column - urgent
single row with multiple column into single column - urgent [message #392156] Mon, 16 March 2009 12:28 Go to next message
manimuthuraman
Messages: 6
Registered: July 2006
Location: chennai
Junior Member
I have a record

col1 col2 col3 col4 col5 col6
------------------------------
abc def ghi jkl mno pqr

i need output as

col
---
abc
def
ghi
jkl
mno
pqr

help me construct a query like this
Re: single row with multiple column into single column - urgent [message #392159 is a reply to message #392156] Mon, 16 March 2009 12:33 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
> urgent
urgent?

Please explain why it is URGENT for me to solve this problem for you.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Re: single row with multiple column into single column - urgent [message #392160 is a reply to message #392159] Mon, 16 March 2009 12:41 Go to previous messageGo to next message
manimuthuraman
Messages: 6
Registered: July 2006
Location: chennai
Junior Member
I need this solution to complete a task in the frontend..
Where i need to populate this result in to a list element..

I did'nt get any idea to solve this..
So it's pending in my case...

Help me out...
Re: single row with multiple column into single column - urgent [message #392162 is a reply to message #392160] Mon, 16 March 2009 12:51 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search for pivot/unpivot/columns to rows...

Regards
Michel
Re: single row with multiple column into single column - urgent [message #392163 is a reply to message #392156] Mon, 16 March 2009 12:52 Go to previous messageGo to next message
manimuthuraman
Messages: 6
Registered: July 2006
Location: chennai
Junior Member
Thx..am using oracle 8i. is it applicable for this version?
Re: single row with multiple column into single column - urgent [message #392164 is a reply to message #392156] Mon, 16 March 2009 12:53 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
SQL> SET COLSEP .....
Re: single row with multiple column into single column - urgent [message #392165 is a reply to message #392163] Mon, 16 March 2009 12:56 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
manimuthuraman wrote on Mon, 16 March 2009 18:52
Thx..am using oracle 8i. is it applicable for this version?

Yes, some of them.

Regards
Michel

Re: single row with multiple column into single column - urgent [message #392169 is a reply to message #392156] Mon, 16 March 2009 13:55 Go to previous messageGo to next message
manimuthuraman
Messages: 6
Registered: July 2006
Location: chennai
Junior Member
Any sample query from anyone...
seems pivot wontwork in 8i
Re: single row with multiple column into single column - urgent [message #392170 is a reply to message #392169] Mon, 16 March 2009 14:00 Go to previous messageGo to next message
ThomasG
Messages: 3185
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Then try a different method COMMA_TO_TABLE and/or str2tab come to mind as search keywords.

The chances of someone else still having a de-supported for years 8i installed somewhere are pretty slim, so you are the the only one who can test the different methods.

Also "wontwork" is not an Oracle error message.

Re: single row with multiple column into single column - urgent [message #392171 is a reply to message #392169] Mon, 16 March 2009 14:30 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
manimuthuraman wrote on Mon, 16 March 2009 19:55
Any sample query from anyone...
seems pivot wontwork in 8i

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

Use SQL*Plus and copy and paste what you tried.

Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel


Re: single row with multiple column into single column - urgent [message #392172 is a reply to message #392156] Mon, 16 March 2009 14:37 Go to previous messageGo to next message
manimuthuraman
Messages: 6
Registered: July 2006
Location: chennai
Junior Member
COMMA_TO_TABLE

i got the following code...
while i search...

-- do i concatenate col...coln and shall i pass it as a parameter ??


CREATE OR REPLACE PROCEDURE TEST_SHOW_FROM_LIST (
empno_list IN VARCHAR2)
IS
empnos DBMS_UTILITY.UNCL_ARRAY;
numemps INTEGER;
BEGIN
DBMS_UTILITY.COMMA_TO_TABLE (empno_list, numemps, empnos);
DBMS_OUTPUT.PUT_LINE('');
FOR rownum IN 1 .. numemps
LOOP
DBMS_OUTPUT.PUT_LINE('EMP - '||empnos(rownum));
END LOOP;
END;
/

BEGIN
TEST_SHOW_FROM_LIST('AA,BB,CC,DD,EE');
END;
/
Re: single row with multiple column into single column - urgent [message #392173 is a reply to message #392172] Mon, 16 March 2009 14:54 Go to previous messageGo to next message
ThomasG
Messages: 3185
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Wait... The values ARE already in different columns? (Which would have been better visible, if you had formatted your post)

Then why don't you just do a

select col1 from table
union all
select col2 from table
union all
select col3 from table
union all
select col4 from table
union all
select col5 from table
union all
select col6 from table
Re: single row with multiple column into single column - urgent [message #392174 is a reply to message #392172] Mon, 16 March 2009 15:11 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why don't you follow guide as requested?
Why don't you answer the question we posted?
Why are you expected we answer in this case?

Regards
Michel
Re: single row with multiple column into single column - urgent [message #392186 is a reply to message #392156] Mon, 16 March 2009 23:16 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Quote:

I need this solution to complete a task in the frontend..


Which front-end you are talking about ?? Java ? Forms ??

Regards,
Ashoka BL
Bengaluru
Re: single row with multiple column into single column - urgent [message #436053 is a reply to message #392156] Mon, 21 December 2009 04:05 Go to previous messageGo to next message
sasi2005
Messages: 1
Registered: August 2005
Location: Chennai
Junior Member
select distinct substr(a.dept_no,1,3) as result1 from sam_test a
union
select distinct substr(b.dept_name,1,9) as result1 from sam_test b

RESULT1
----------
100
200
300
400
500
HR
IT
Marketing
Research
Sales
Razz
Re: single row with multiple column into single column - urgent [message #436062 is a reply to message #436053] Mon, 21 December 2009 04:53 Go to previous message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How does this answer the question???

Regards
Michel
Previous Topic: left and right functions?
Next Topic: Convert from Georgian Date(English Date) To Arabic Hijri Date
Goto Forum:
  


Current Time: Wed Sep 28 21:11:47 CDT 2016

Total time taken to generate the page: 0.11859 seconds