Home » SQL & PL/SQL » SQL & PL/SQL » Merge 2 or more rows into 1 (oracle 12c)
Merge 2 or more rows into 1 [message #668923] Fri, 23 March 2018 10:12 Go to next message
laredoeneko
Messages: 21
Registered: November 2017
Junior Member
Hello,
i donot know if this is possible, i have a table with information like this:

element1	DOWN@14.29
element1	UP@85.71
element2	DOWN@50
element2	UP@50


is it possible get a query to get the output like this, group rows for the first column:

element1	DOWN@14.29 UP@85.71
element2	DOWN@50    UP@50

thank you very much
Cesar



SET DEFINE OFF;
Insert into PORTALES.TEST123
   (SP2, PERCENT)
 Values
   ('element1', 'DOWN@14.29');
Insert into PORTALES.TEST123
   (SP2, PERCENT)
 Values
   ('element1
', 'UP@85.71');
Insert into PORTALES.TEST123
   (SP2, PERCENT)
 Values
   ('element2', 'DOWN@50');
Insert into PORTALES.TEST123
   (SP2, PERCENT)
 Values
   ('element2', 'UP@50
');
COMMIT;
Re: Merge 2 or more rows into 1 [message #668924 is a reply to message #668923] Fri, 23 March 2018 11:31 Go to previous messageGo to next message
Michel Cadot
Messages: 66808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Group rows in 2 columns or as many columns as there are values?
Is the number of values (per first column) fixed?
Is there a maximum number of such values?
Where is the CREATE TABLE statement?
Are you sure there are new lines inside the values? Please double check your statements.
And the CREATE USER PORTALES one if you want you use this account? otherwise don't put it in the statements of your test case.

Re: Merge 2 or more rows into 1 [message #668926 is a reply to message #668924] Fri, 23 March 2018 13:13 Go to previous messageGo to next message
Bill B
Messages: 1927
Registered: December 2004
Senior Member
The following code will do what you want

SELECT Sp2, LISTAGG (Percent, ' ') WITHIN GROUP (ORDER BY Percent) AS Listing
  FROM Test123
  GROUP BY SP2
  ORDER BY SP2;
Re: Merge 2 or more rows into 1 [message #668927 is a reply to message #668926] Fri, 23 March 2018 13:22 Go to previous messageGo to next message
Michel Cadot
Messages: 66808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Unless the result is in multiple columns, one per value...

Re: Merge 2 or more rows into 1 [message #668929 is a reply to message #668926] Fri, 23 March 2018 15:38 Go to previous messageGo to next message
laredoeneko
Messages: 21
Registered: November 2017
Junior Member
thank you very much, works perfect!!!!!
Re: Merge 2 or more rows into 1 [message #668930 is a reply to message #668924] Fri, 23 March 2018 15:39 Go to previous messageGo to next message
laredoeneko
Messages: 21
Registered: November 2017
Junior Member
thank you you are right, the code is not correct.
Re: Merge 2 or more rows into 1 [message #668931 is a reply to message #668930] Sat, 24 March 2018 01:11 Go to previous messageGo to next message
Michel Cadot
Messages: 66808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
... as the specification... Smile

Please note you have to also give the column headers in your result chart.

You say the query is perfect but compare the result:
Yours:
element1 DOWN@14.29 UP@85.71
element2 DOWN@50    UP@50
The query:
SQL> SELECT Sp2, LISTAGG (Percent, ' ') WITHIN GROUP (ORDER BY Percent) AS Listing
  2    FROM Test123
  3    GROUP BY SP2
  4    ORDER BY SP2;
SP2      LISTING
-------- --------------------
element1 DOWN@14.29 UP@85.71
element2 DOWN@50 UP@50
Not exactly the same one isn't it?
So query does not "work perfect" or what you posted is not correct.

[Updated on: Sat, 24 March 2018 01:20]

Report message to a moderator

Re: Merge 2 or more rows into 1 [message #668932 is a reply to message #668923] Sat, 24 March 2018 02:35 Go to previous message
John Watson
Messages: 8137
Registered: January 2010
Location: Global Village
Senior Member
Another solution,
orclx>
orclx> select sp2,max(percent),min(percent) from test123 group by sp2;

SP2        MAX(PERCEN MIN(PERCEN
---------- ---------- ----------
element2   UP@50      DOWN@50
element1   UP@85.71   DOWN@14.29

orclx>
Previous Topic: Display Comma Separated Values
Next Topic: How to log error using UTL_CALL_STACK
Goto Forum:
  


Current Time: Wed Jan 29 01:52:57 CST 2020