Home » SQL & PL/SQL » SQL & PL/SQL » convert data to table/inline view
convert data to table/inline view [message #584267] Fri, 10 May 2013 14:08 Go to next message
tmcallister
Messages: 97
Registered: December 2007
Member
Is there a function that allows the following?

select SOME_FUNCTION('N','E','S','W') from dual;


That returns
N
E
S
W


Currently I'm just doing the following

WITH direction AS
       (SELECT 'N' dir FROM DUAL
        UNION
        SELECT 'E' FROM DUAL
        UNION
        SELECT 'S' FROM DUAL
        UNION
        SELECT 'W' FROM DUAL)
SELECT   *
FROM     direction;


And I recall seeing a better way; but my memory and google skills fail me.
Re: convert data to table/inline view [message #584268 is a reply to message #584267] Fri, 10 May 2013 14:17 Go to previous messageGo to next message
Michel Cadot
Messages: 58933
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A pipelined function.

Regards
Michel
Re: convert data to table/inline view [message #584269 is a reply to message #584268] Fri, 10 May 2013 14:47 Go to previous messageGo to next message
tmcallister
Messages: 97
Registered: December 2007
Member
Ah, yes that would work; but in this case I'd probably just stick to the dual unions. Not quite what I was remembering; I seem to recall some sort of set/group operation build in function; but maybe my memory is just thinking wishfully.

Thanks anyway!
Re: convert data to table/inline view [message #584271 is a reply to message #584269] Fri, 10 May 2013 15:10 Go to previous messageGo to next message
Michel Cadot
Messages: 58933
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is your actual input? A single string with comma separated values or an array of strings?

Regards
Michel
Re: convert data to table/inline view [message #584272 is a reply to message #584271] Fri, 10 May 2013 15:12 Go to previous message
Michel Cadot
Messages: 58933
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe you are thinking about this:
SQL> select * from table(sys.odcivarchar2list('N','E','S','W'));
COLUMN_VALUE
-------------------------------------------------------------------
N
E
S
W

Regards
Michel
Previous Topic: Tricks to select a whole row based on a max value
Next Topic: Fastest method for one to one update
Goto Forum:
  


Current Time: Thu Aug 28 18:53:54 CDT 2014

Total time taken to generate the page: 0.21514 seconds