This discussion is archived
4 Replies Latest reply: Nov 27, 2013 1:46 PM by OracleDeveloper RSS

Row to Column Conversion Question

OracleDeveloper Newbie
Currently Being Moderated

Hi All,

           Could you please help on this sql query statement:

 

SELECT BENEFIT FROM TEST_T ;

 

BENEFIT

--------------

QO,1G,1U,1W

 

Out Put Needed like this

 

BENEFIT

----------

QO

1G

1U

1W

 

Thanks,

  • 1. Re: Row to Column Conversion Question
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

    Here's one way:

     

    SELECT  REGEXP_SUBSTR ( benefit

                          , '[^,]+'

                          , 1

                          , LEVEL

                          )    AS bnft

    FROM    test_t

    CONNECT BY   LEVEL              <= REGEXP_COUNT (benefit, '[^,]+')

            AND  PRIOR benefit      = benefit

            AND  PRIOR SYS_GUID ()  IS NOT NULL

    ;

    Like everything else, it depends on your data, your requirements, and your Oracle version.

  • 2. Re: Row to Column Conversion Question
    OracleDeveloper Newbie
    Currently Being Moderated

    Please check this error below :

     

    ORA-00904: "REGEXP_COUNT": invalid identifier

    00904. 00000 -  "%s: invalid identifier"

    *Cause:   

    *Action:

    Error at Line: 7 Column: 36

     

    BANNER                                                        

    ----------------------------------------------------------------

    Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi

    PL/SQL Release 10.2.0.5.0 - Production                          

    CORE 10.2.0.5.0 Production                                        

    TNS for HPUX: Version 10.2.0.5.0 - Production                   

    NLSRTL Version 10.2.0.5.0 - Production                          

  • 3. Re: Row to Column Conversion Question
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

    As I said, it depends on your data, your requirements and your version.

     

    REGEXP_COUNT was new in Oracle 11.1.  Since you're using an earlier version, you can see how many comma-delimited sub-strings you have by seeing how much shorter the string gets if you remove all the commas:

    1 + LENGTH (benefit)

      - LENGTH (REPLACE (benefit, ','))

    This assumes you don't have data with mutiple consecutive commas such as 'AB,,,,CD', but it might not cause any terrible results even if you do.

    The way I used CONNECT BY won't work in Oracle 10, either.

     

    Now I know everything I need to know about your Oracle version, but I don't know anything about your data or your requirements.  Do you want me to spend my time writing solutions that assume things about your data and your requirments, so you can spend your time trying them only to see that they don't do what you need?

    Explain what your data is like.  Do you have a fixed number of comma-delimited parts (e.g., always 4 parts).  If not, do you have an upper bound (e.g., never more than 10 parts)?  Can you have multiple consecutive commas?

    Explain what you need to get from that data.  Will you always be splitting one string into parts, as in the example you posted, or will you need to split multiple stings in the same query?  Will the correct results ever contain NULL?

    Post a little sample data (CREATE TABLE and INSERT statements), showing all special cases you need to handle.  Post the results you want from that data.

    See the forum FAQ: https://forums.oracle.com/message/9362002

  • 4. Re: Row to Column Conversion Question
    OracleDeveloper Newbie
    Currently Being Moderated

       Thank you so much frankkulah. I Got..

     

     

    WITH TEST AS (SELECT 'QO,1G,1U,1W' str FROM dual )

       SELECT regexp_substr (str, '[^,]+', 1, ROWNUM) SPLIT

         FROM TEST

       CONNECT BY LEVEL*1 <= LENGTH (regexp_replace (str, '[^,]+'))+1;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points