Home » SQL & PL/SQL » SQL & PL/SQL » Is there a better way to do this in Oracle - Generate series with other entries (Oracle >= 11.2)
Is there a better way to do this in Oracle - Generate series with other entries [message #685885] Thu, 14 April 2022 08:37 Go to next message
dragam
Messages: 31
Registered: May 2012
Location: Ireland
Member
Hi,

I came across this problem recently (the simple table DDL is available in the links below):


I have a table:

NAME 	    X
George 	        2
Martina 	5
and the question was how to generate this:

NAME 	X
George 	1
George 	2
Martina 	1
Martina 	2
Martina 	3
Martina 	4
Martina 	5
Now, I did it this way:

WITH RECURSIVE cte AS
(
  SELECT name, 1 AS x FROM total_hours_played
    UNION ALL
  SELECT name, x + 1 FROM cte
  WHERE x < 
    (SELECT MAX(hpt) FROM total_hours_played 
       WHERE name = (SELECT name FROM total_hours_played WHERE name = cte.name))
)
SELECT * FROM cte
ORDER BY name, x;
See the link here (Oracle) and here (PostgreSQL).


As you can see,they are identical.

However, there's a very elegant PostgreSQL solution (link here) as follows:


SELECT
  name, 
  GENERATE_SERIES(1, x)
FROM
  total_hours_played;
My question is, is there any way of doing something similar to the PostgreSQL solution with Oracle?

Any other possible solutions also appreciated.

TIA and rgs.






Re: Is there a better way to do this in Oracle - Generate series with other entries [message #685888 is a reply to message #685885] Thu, 14 April 2022 09:05 Go to previous message
Michel Cadot
Messages: 68226
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

http://www.orafaq.com/forum/mv/msg/95011/493455/#msg_493455 (second solution, first one was for old versions of Oracle, the post is more than 10 years old).

[Updated on: Thu, 14 April 2022 09:06]

Report message to a moderator

Previous Topic: question on solution provided
Next Topic: How to append a header on the top of the CSV file
Goto Forum:
  


Current Time: Mon Jul 04 16:49:00 CDT 2022