Home » SQL & PL/SQL » SQL & PL/SQL » How to interesting select * (Oracle 9i)
How to interesting select * [message #329329] Wed, 25 June 2008 02:21 Go to next message
Tlg13team
Messages: 100
Registered: June 2008
Location: MGL
Senior Member
hi all,
i can't exactly explain own problem, but show below draft.

select *
from table1

result:
col1 col2
1 aaaa
1 bbbb
2 rrrr
2 rrrr
2 eeee
3 wwww
4 pppp

I need below result:

col1 col2
1 aaaa bbbb
2 rrrr rrrr eeee
3 wwww
4 pppp

how to select that result from table1.

[Updated on: Wed, 25 June 2008 02:29]

Report message to a moderator

Re: How to interesting select * [message #329337 is a reply to message #329329] Wed, 25 June 2008 02:44 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Use DECODE function
Re: How to interesting select * [message #329338 is a reply to message #329329] Wed, 25 June 2008 02:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/
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) and align the columns in result.
Use the "Preview Message" button to verify.

2/
Always post your Oracle version (4 decimals).

3/
Search for "pivot" this question is asked and answered each and every week.

Regards
Michel
Re: How to interesting select * [message #344673 is a reply to message #329338] Sun, 31 August 2008 22:29 Go to previous messageGo to next message
Tlg13team
Messages: 100
Registered: June 2008
Location: MGL
Senior Member
How to using decode function?
What's syntax?
Re: How to interesting select * [message #344711 is a reply to message #344673] Mon, 01 September 2008 01:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL Reference

Regards
Michel
Re: How to interesting select * [message #344981 is a reply to message #329329] Tue, 02 September 2008 01:32 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
CREATE TABLE table1(col1 number,col2 varchar2(30))

INSERT INTO table1 values(1,'aaaa')
INSERT INTO table1 values(1,'bbbb')
INSERT INTO table1 values(2,'rrrr')
INSERT INTO table1 values(2,'rrrr')
INSERT INTO table1 values(2,'eeee')
INSERT INTO table1 values(3,'wwww')
INSERT INTO table1 values(4,'pppp')

SELECT 
col1
,MIN(DECODE(rank,1,col2))|| '  ' || MIN(DECODE(rank,2,col2)) || '  ' || MIN(DECODE(rank,3,col2))
from
(
SELECT 
col1,col2,ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col2) rank 
FROM table1
)
GROUP BY col1

Re: How to interesting select * [message #344997 is a reply to message #344981] Tue, 02 September 2008 02:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What if there are more than 3 values?

Regards
Michel
Re: How to interesting select * [message #345005 is a reply to message #329329] Tue, 02 September 2008 02:28 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Use the STRAGG Function


SELECT 
col1,STRAGG(col2) 
FROM table1
GROUP BY col1



If function not available,

use - http://www.sqlsnippets.com/en/topic-11591.html
Re: How to interesting select * [message #345300 is a reply to message #345005] Tue, 02 September 2008 20:39 Go to previous message
Tlg13team
Messages: 100
Registered: June 2008
Location: MGL
Senior Member
Thank you for all replays.
spmano1983 post is very attractive Razz

I had resolve own problem Smile
Previous Topic: CREATE VIEW
Next Topic: differnece b/w bulk collect/forall and bulk collect/for
Goto Forum:
  


Current Time: Sun Dec 04 00:40:06 CST 2016

Total time taken to generate the page: 0.10706 seconds