Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2010
    Posts
    9

    Question Unanswered: Unpivoting Large Tables

    I have a 27 million row table in the following format:

    MEDCLM_MTH_SUM_KEY PRIMARY_DIAG_CD DIAG_CD2 DIAG_CD3 DIAG_CD4 DIAG_CD5 DIAG_CD6 DIAG_CD7 DIAG_CD8 DIAG_CD9 DIAG_CD10
    2212990780 5552 78907 53170 5368
    2231127242 V5481 7812 71595 4019 2761 2859 496 V4364 30501

    I need to unpivot this data to get it to look like this:

    MEDCLM_MTH_SUM_KEY DIAG_CD_LEVEL DIAG_CD
    2212990780 PRIMARY_DIAG_CD 5552
    2212990780 DIAG_CD2 78907
    2212990780 DIAG_CD3 53170
    2212990780 DIAG_CD4 5368
    2231127242 PRIMARY_DIAG_CD V5481
    2231127242 DIAG_CD2 7812
    2231127242 DIAG_CD3 71595
    2231127242 DIAG_CD4 4019
    2231127242 DIAG_CD5 2761
    2231127242 DIAG_CD6 2859
    2231127242 DIAG_CD7 496
    2231127242 DIAG_CD8 V4364
    2231127242 DIAG_CD9 30501


    This is the code that I have:

    Code:
    select medclm_mth_sum_key,
           decode(unpivot_row, 1, 'PRIMARY_DIAG_CD', 2, 'DIAG_CD2', 3, 'DIAG_CD3', 4, 'DIAG_CD4', 5, 'DIAG_CD5', 6, 'DIAG_CD6', 7,
                   'DIAG_CD7', 8, 'DIAG_CD8', 9, 'DIAG_CD9', 10, 'DIAG_CD10') as diag_cd_level,
           decode(unpivot_row, 1, PRIMARY_DIAG_CD, 2, DIAG_CD2, 3, DIAG_CD3, 4, DIAG_CD4, 5, DIAG_CD5, 6, DIAG_CD6, 7, DIAG_CD7, 8,
                   DIAG_CD8, 9, DIAG_CD9, 10, DIAG_CD10) as diag_cd
    from medclm
    cross join (select level as unpivot_row from dual connect by level <= 10)
    where decode(unpivot_row, 1, PRIMARY_DIAG_CD, 2, DIAG_CD2, 3, DIAG_CD3, 4, DIAG_CD4, 5, DIAG_CD5, 6, DIAG_CD6, 7, DIAG_CD7, 8,
                 DIAG_CD8, 9, DIAG_CD9, 10, DIAG_CD10) is not null
    order by 1,2
    I was wondering if there was a quicker, more efficient way to do this.

    Thanks in advance.

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    What version are you using. If it is 11g then see

    pivot and unpivot queries in 11g
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Nov 2010
    Posts
    9
    Sorry, I meant to mention this in my original post: I'm using version 10g, so the UNPIVOT operator is not available.

Posting Permissions

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