Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2015
    Posts
    1

    Unanswered: Split one row to many rows

    Hi all,
    I am trying to get this implement this logic. Please provide assistance, since I dont have much pl/sql coding knowledge.

    I want to convert one single row into multiple rows based on the contents present in the individual rows.

    VOD_CATEGORY_ID ISBBV ISIPTV ISMOBILE ISADOBE ISAD

    155 244 0 0 0 0
    352 0 0 0 247 0
    286 0 245 246 0 248
    301 244 0 0 247 0

    Then, the target table will be:


    SERVICE_PROFILE_ID PRODUCT_TYPE_ID

    155 244
    352 247
    286 245
    286 246
    286 248
    301 244
    301 247

    Thanks
    Prithvi

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Here's a simple solution; nothing smart in it, really.
    Code:
    SQL> with test as
      2    (select 155 vod_category_id,
      3            244 isbbv,
      4            0   isiptv,
      5            0   ismobile,
      6            0   isadobe,
      7            0   isad
      8       from dual union all
      9     select 352, 0, 0, 0, 247, 0 from dual union all
     10     select 286, 0, 245, 246, 0, 248 from dual union all
     11     select 301, 244, 0, 0, 247, 0 from dual
     12    )
     13  select vod_category_id service_profile_id,
     14         isbbv           product_type_id
     15    from test
     16    where isbbv > 0 union all
     17  select vod_category_id, isiptv   from test where isiptv   > 0 union all
     18  select vod_category_id, ismobile from test where ismobile > 0 union all
     19  select vod_category_id, isadobe  from test where isadobe  > 0 union all
     20  select vod_category_id, isad     from test where isad     > 0
     21  order by 1, 2;
    
    SERVICE_PROFILE_ID PRODUCT_TYPE_ID
    ------------------ ---------------
                   155             244
                   286             245
                   286             246
                   286             248
                   301             244
                   301             247
                   352             247
    
    7 rows selected.
    
    SQL>

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •