Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Apr 2012
    Posts
    32

    Unanswered: Help on UNPIVOT command

    Hello all

    I have a query where I'm doing an unpivot all function in order to assign hard coded labels to values that I'm reading from from a table. The query is:

    WITH T AS
    (
    SELECT
    PRO.NB_PR_MENS AS MONT_1,
    PRO.NB_PR_CUM AS MONT_2,
    PRO.NB_PR_ETB_MENS AS MONT_3,
    PRO.NB_PR_ETB_CUM AS MONT_4,
    PRO.NB_PRF_LIB_MENS AS MONT_5,
    PRO.NB_PRF_LIB_CUM AS MONT_6,
    PRO.NB_PR_ETB_MENS AS MONT_7,
    PRO.NB_PR_ETB_CUM AS MONT_8,
    PRO.NB_PR_STO_TOT AS MONT_9,
    PRO.NB_PR_STO_HG AS MONT_10,
    PRO.NB_PR_STO_GP AS MONT_11,
    PRO.NB_PR_STO_GF AS MONT_12
    FROM
    SAS_BPCE.BPCE_APC_PROGPACT PRO
    )
    SELECT
    CODE_INDC,
    VALR_INDC
    FROM
    T
    UNPIVOT INCLUDE NULLS
    (
    VALR_INDC FOR CODE_INDC IN
    (
    MONT_1 AS 'NBPRMENS',
    MONT_2 AS 'NBPRCUM',
    MONT_3 AS 'NBPRETBMENS',
    MONT_4 AS 'NBPRETBCUM',
    MONT_5 AS 'NBPRFLIBMENS',
    MONT_6 AS 'NBPRFLIBCUM',
    MONT_7 AS 'NBPRSUPACTMENS',
    MONT_8 AS 'NBPRACTCUM',
    MONT_9 AS 'NBPRSTOTOT',
    MONT_10 AS 'NBPRSTOHG',
    MONT_11 AS 'NBPRSTOGP',
    MONT_12 AS 'NBPRSTOGF'
    )
    )

    In the WITH T section is my normal SELECT statement which on its own reads all the values from a table and assigns them to columns with names such as MONT_1, MONT_2, etc.

    In the second section, these columns MONT_1, MONT_2, etc. are assigned to a single column VALR_INDC (thus the need for UNPIVOT) alongside a column called CODE_INDC that contains the hard coded label.

    Now I need to associate TWO labels to each value. I need to have a third column which could align with each pair of CODE_INDC and VALR_INDC.

    Is it possible to associate two columns to an unpivoted column? If so, I'd be grateful if you guys could tell me how to go about it and what the correct syntax is.

    Thanks a lot

    Tony

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Perhaps if you replace the "T" table query with data and provide the required result based on that data, we may be able to help.


    PS: Please also clarify your question:
    Is it possible to associate two columns to an unpivoted column?
    Last edited by LKBrwn_DBA; 07-03-14 at 14:45.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Apr 2012
    Posts
    32
    A SELECT query from the original table is:

    SELECT 'PROGPACT' AS LIBL_SOUR, PRO.NB_PR_MENS AS MONT_1,
    PRO.NB_PR_CUM AS MONT_2 FROM SAS_BPCE.BPCE_APC_PROGPACT PRO

    The result is in the attachment ProgpactOrig.JPG.

    My objective here is to split each row containing a MONT_1 and a MONT_2 into two rows thus assigning MONT_1 & MONT_2 to a new variable called VAL (which signifies VALUE) and a add a third field called CODE which has one value for MONT_1 and another for MONT_2. The original 5 lines now thus become 10. I am doing this now using the following query:

    WITH T AS
    (
    SELECT
    'PROGPACT' AS LIBL_SOUR,
    PRO.NB_PR_MENS AS MONT_1,
    PRO.NB_PR_CUM AS MONT_2
    FROM
    SAS_BPCE.BPCE_APC_PROGPACT PRO
    )
    SELECT
    LIBL_SOUR ,
    CODE,
    VAL
    FROM
    T
    UNPIVOT INCLUDE NULLS
    (
    VAL FOR CODE IN
    (
    MONT_1 AS 'NBPRMENS',
    MONT_2 AS 'NBPRCUM'
    )
    )

    So the T is the same as the SELECT which I've detailed above.

    The code corresponding to MONT_1 is 'NBPRMENS' and that corresponding to MONT_2 is NBPRCUM as is clear from the attachment ProgpactUnPivoted.JPG.

    What I now need to do is associate a second CODE to each line. The total number of lines after splitting still remains 10 but instead of 3 fields I now need 4. the 4th field could be CODE_2 (or anything of that matter) and it would contain a set of hard coded values like CODE. So hypothetically speaking I could have NBPRMENS_NEW corresponding to MONT_1 and NBPRCUM_2 corresponding to MONT_2.

    I hope this is clear guys.

    All your help would be much appreciated.

    Thanks & regards

    Tony
    Attached Thumbnails Attached Thumbnails ProgpactOrig.JPG   ProgpactUnPivoted.JPG  

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down

    Quote Originally Posted by S. BASU View Post
    Hello all
    . . . E t c . . .

    Now I need to associate TWO labels to each value. I need to have a third column which could align with each pair of CODE_INDC and VALR_INDC.

    Is it possible to associate two columns to an unpivoted column?
    Your requirements are still incomplete:

    It is yet not clear what is this third column? where does it come from? which are the rules to align with each pair of CODE_INDC and VALR_INDC?

    You did not provide a sample of the source data and expected results.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Apr 2012
    Posts
    32
    Quote Originally Posted by LKBrwn_DBA View Post
    You did not provide a sample of the source data and expected results.
    Yes I did. the source data is in the first JPG.

    The second JPG is the current result which shows a hard coded value associated with each VAL. Now what I want is a second hard coded value alongside each pair of CODE and VAL.

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Question

    Quote Originally Posted by S. BASU View Post
    Yes I did. the source data is in the first JPG.

    The second JPG is the current result which shows a hard coded value associated with each VAL. Now what I want is a second hard coded value alongside each pair of CODE and VAL.
    And again: where does this second hard-coded value come from? Is is the same for all CODE and VAL?

    Like this:
    Code:
    WITH T ( Libl_Sour, Mont_1, Mont_2, Mont_3, Mont_4, Mont_5 )
         AS (SELECT 'PROGPACT', 198, 788, 138, 378, 838 FROM DUAL         UNION ALL
             SELECT 'PROGPACT', 108, 853, 239, 1002, 154 FROM DUAL         UNION ALL
             SELECT 'PROGPACT', 573, 150, 735, 198, 788 FROM DUAL         UNION ALL
             SELECT 'PROGPACT', 138, 378, 838, 108, 853 FROM DUAL         UNION ALL
             SELECT 'PROGPACT', 239, 1002, 154, 573, 150 FROM DUAL         UNION ALL
             SELECT 'PROGPACT', 735, 138, 239, 735, 838 FROM DUAL)
    SELECT Libl_Sour, Code_Indc, Valr_Indc
      FROM T UNPIVOT INCLUDE NULLS (Valr_Indc
             FOR Code_Indc
             IN  (Mont_1 AS 'NBPRMENS'
               , Mont_2 AS 'NBPRCUM'
               , Mont_3 AS 'NBPRETBMENS'
               , Mont_4 AS 'NBPRETBCUM'
               , Mont_5 AS 'NBPRFLIBMENS'
    . . .   E t c  . . .
    LIBL_SOUR CODE_INDC     VALR_INDC
    --------- ------------ ----------
    PROGPACT  NBPRCUM             138
    PROGPACT  NBPRCUM             150
    PROGPACT  NBPRCUM             378
    PROGPACT  NBPRCUM             788
    PROGPACT  NBPRCUM             853
    PROGPACT  NBPRCUM            1002
    PROGPACT  NBPRETBCUM          108
    PROGPACT  NBPRETBCUM          198
    PROGPACT  NBPRETBCUM          378
    PROGPACT  NBPRETBCUM          573
    PROGPACT  NBPRETBCUM          735
    PROGPACT  NBPRETBCUM         1002
    PROGPACT  NBPRETBMENS         138
    PROGPACT  NBPRETBMENS         154
    PROGPACT  NBPRETBMENS         239
    PROGPACT  NBPRETBMENS         239
    PROGPACT  NBPRETBMENS         735
    PROGPACT  NBPRETBMENS         838
    PROGPACT  NBPRFLIBMENS        150
    PROGPACT  NBPRFLIBMENS        154
    PROGPACT  NBPRFLIBMENS        788
    PROGPACT  NBPRFLIBMENS        838
    PROGPACT  NBPRFLIBMENS        838
    PROGPACT  NBPRFLIBMENS        853
    PROGPACT  NBPRMENS            108
    PROGPACT  NBPRMENS            138
    PROGPACT  NBPRMENS            198
    PROGPACT  NBPRMENS            239
    PROGPACT  NBPRMENS            573
    PROGPACT  NBPRMENS            735
    - - - ...
    ))
    Last edited by LKBrwn_DBA; 07-08-14 at 12:38.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  7. #7
    Join Date
    Apr 2012
    Posts
    32
    The query that you've posted is what I'm using now and it returns the result set as you've shown.

    What I need is a fourth column (let's call it CODE_INDC_NEW) and thus a result set like:

    Code:
    LIBL_SOUR	CODE_INDC_NEW	CODE_INDC	VALR_INDC
    			
    PROGPACT	NBPRMENS	NBPRMENS_NEW	198
    PROGPACT	NBPRCUM	NBPRCUM_NEW	788
    PROGPACT	NBPRETBMENS	NBPRETBMENS_NEW	138
    PROGPACT	NBPRETBCUM	NBPRETBCUM_NEW	378
    PROGPACT	NBPRFLIBMENS	NBPRFLIBMENS_NEW	838
    PROGPACT	NBPRMENS	NBPRMENS_NEW	108
    PROGPACT	NBPRCUM	NBPRCUM_NEW	853
    PROGPACT	NBPRETBMENS	NBPRETBMENS_NEW	239
    PROGPACT	NBPRETBCUM	NBPRETBCUM_NEW	1002
    PROGPACT	NBPRFLIBMENS	NBPRFLIBMENS_NEW	154
    PROGPACT	NBPRMENS	NBPRMENS_NEW	573
    PROGPACT	NBPRCUM	NBPRCUM_NEW	150
    PROGPACT	NBPRETBMENS	NBPRETBMENS_NEW	735
    PROGPACT	NBPRETBCUM	NBPRETBCUM_NEW	198
    PROGPACT	NBPRFLIBMENS	NBPRFLIBMENS_NEW	788
    PROGPACT	NBPRMENS	NBPRMENS_NEW	138
    PROGPACT	NBPRCUM	NBPRCUM_NEW	378
    PROGPACT	NBPRETBMENS	NBPRETBMENS_NEW	838
    PROGPACT	NBPRETBCUM	NBPRETBCUM_NEW	108
    PROGPACT	NBPRFLIBMENS	NBPRFLIBMENS_NEW	853
    PROGPACT	NBPRMENS	NBPRMENS_NEW	239
    PROGPACT	NBPRCUM	NBPRCUM_NEW	1002
    PROGPACT	NBPRETBMENS	NBPRETBMENS_NEW	154
    PROGPACT	NBPRETBCUM	NBPRETBCUM_NEW	573
    PROGPACT	NBPRFLIBMENS	NBPRFLIBMENS_NEW	150
    PROGPACT	NBPRMENS	NBPRMENS_NEW	735
    PROGPACT	NBPRCUM	NBPRCUM_NEW	138
    PROGPACT	NBPRETBMENS	NBPRETBMENS_NEW	239
    PROGPACT	NBPRETBCUM	NBPRETBCUM_NEW	735
    PROGPACT	NBPRFLIBMENS	NBPRFLIBMENS_NEW	838
    I hope this is clear.

    Please don't hesitate to get back to me if required

    Cheers

    Tony

  8. #8
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by S. BASU View Post
    . . .
    What I need is . . .
    I hope this is clear.
    Like this?
    Code:
    SQL> With T ( Libl_Sour, Mont_1, Mont_2, Mont_3, Mont_4, Mont_5 )
      2    As (
      3      Select 'PROGPACT', 198, 788, 138, 378, 838  From DUAL Union All
      4      Select 'PROGPACT', 108, 853, 239, 1002, 154 From DUAL Union All
      5      Select 'PROGPACT', 573, 150, 735, 198, 788  From DUAL Union All
      6      Select 'PROGPACT', 138, 378, 838, 108, 853  From DUAL Union All
      7      Select 'PROGPACT', 239, 1002, 154, 573, 150 From DUAL Union All
      8      Select 'PROGPACT', 735, 138, 239, 735, 838  From DUAL)
      9  --
     10  Select Code_Indc, Code_Indc || '_NEW' Code_Indc_New, Valr_Indc
     11    From T Unpivot Include Nulls (Valr_Indc
     12           For Code_Indc
     13           In  (Mont_1 As 'NBPRMENS'
     14             , Mont_2 As 'NBPRCUM'
     15             , Mont_3 As 'NBPRETBMENS'
     16             , Mont_4 As 'NBPRETBCUM'
     17             , Mont_5 As 'NBPRFLIBMENS'))
     18  /
    
    CODE_INDC            CODE_INDC_NEW        VALR_INDC
    -------------------- -------------------- ---------
    NBPRMENS             NBPRMENS_NEW               198
    NBPRCUM              NBPRCUM_NEW                788
    NBPRETBMENS          NBPRETBMENS_NEW            138
    NBPRETBCUM           NBPRETBCUM_NEW             378
    NBPRFLIBMENS         NBPRFLIBMENS_NEW           838
    NBPRMENS             NBPRMENS_NEW               108
    NBPRCUM              NBPRCUM_NEW                853
    NBPRETBMENS          NBPRETBMENS_NEW            239
    NBPRETBCUM           NBPRETBCUM_NEW           1,002
    NBPRFLIBMENS         NBPRFLIBMENS_NEW           154
    NBPRMENS             NBPRMENS_NEW               573
    NBPRCUM              NBPRCUM_NEW                150
    NBPRETBMENS          NBPRETBMENS_NEW            735
    NBPRETBCUM           NBPRETBCUM_NEW             198
    NBPRFLIBMENS         NBPRFLIBMENS_NEW           788
    NBPRMENS             NBPRMENS_NEW               138
    NBPRCUM              NBPRCUM_NEW                378
    NBPRETBMENS          NBPRETBMENS_NEW            838
    NBPRETBCUM           NBPRETBCUM_NEW             108
    NBPRFLIBMENS         NBPRFLIBMENS_NEW           853
    NBPRMENS             NBPRMENS_NEW               239
    NBPRCUM              NBPRCUM_NEW              1,002
    NBPRETBMENS          NBPRETBMENS_NEW            154
    NBPRETBCUM           NBPRETBCUM_NEW             573
    NBPRFLIBMENS         NBPRFLIBMENS_NEW           150
    NBPRMENS             NBPRMENS_NEW               735
    NBPRCUM              NBPRCUM_NEW                138
    NBPRETBMENS          NBPRETBMENS_NEW            239
    NBPRETBCUM           NBPRETBCUM_NEW             735
    NBPRFLIBMENS         NBPRFLIBMENS_NEW           838
    
    30 rows selected.
    
    SQL>
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  9. #9
    Join Date
    Apr 2012
    Posts
    32
    Quote Originally Posted by LKBrwn_DBA View Post
    Like this?
    Code:
    SQL> With T ( Libl_Sour, Mont_1, Mont_2, Mont_3, Mont_4, Mont_5 )
      2    As (
      3      Select 'PROGPACT', 198, 788, 138, 378, 838  From DUAL Union All
      4      Select 'PROGPACT', 108, 853, 239, 1002, 154 From DUAL Union All
      5      Select 'PROGPACT', 573, 150, 735, 198, 788  From DUAL Union All
      6      Select 'PROGPACT', 138, 378, 838, 108, 853  From DUAL Union All
      7      Select 'PROGPACT', 239, 1002, 154, 573, 150 From DUAL Union All
      8      Select 'PROGPACT', 735, 138, 239, 735, 838  From DUAL)
      9  --
     10  Select Code_Indc, Code_Indc || '_NEW' Code_Indc_New, Valr_Indc
     11    From T Unpivot Include Nulls (Valr_Indc
     12           For Code_Indc
     13           In  (Mont_1 As 'NBPRMENS'
     14             , Mont_2 As 'NBPRCUM'
     15             , Mont_3 As 'NBPRETBMENS'
     16             , Mont_4 As 'NBPRETBCUM'
     17             , Mont_5 As 'NBPRFLIBMENS'))
     18  /
    
    CODE_INDC            CODE_INDC_NEW        VALR_INDC
    -------------------- -------------------- ---------
    NBPRMENS             NBPRMENS_NEW               198
    NBPRCUM              NBPRCUM_NEW                788
    NBPRETBMENS          NBPRETBMENS_NEW            138
    NBPRETBCUM           NBPRETBCUM_NEW             378
    NBPRFLIBMENS         NBPRFLIBMENS_NEW           838
    NBPRMENS             NBPRMENS_NEW               108
    NBPRCUM              NBPRCUM_NEW                853
    NBPRETBMENS          NBPRETBMENS_NEW            239
    NBPRETBCUM           NBPRETBCUM_NEW           1,002
    NBPRFLIBMENS         NBPRFLIBMENS_NEW           154
    NBPRMENS             NBPRMENS_NEW               573
    NBPRCUM              NBPRCUM_NEW                150
    NBPRETBMENS          NBPRETBMENS_NEW            735
    NBPRETBCUM           NBPRETBCUM_NEW             198
    NBPRFLIBMENS         NBPRFLIBMENS_NEW           788
    NBPRMENS             NBPRMENS_NEW               138
    NBPRCUM              NBPRCUM_NEW                378
    NBPRETBMENS          NBPRETBMENS_NEW            838
    NBPRETBCUM           NBPRETBCUM_NEW             108
    NBPRFLIBMENS         NBPRFLIBMENS_NEW           853
    NBPRMENS             NBPRMENS_NEW               239
    NBPRCUM              NBPRCUM_NEW              1,002
    NBPRETBMENS          NBPRETBMENS_NEW            154
    NBPRETBCUM           NBPRETBCUM_NEW             573
    NBPRFLIBMENS         NBPRFLIBMENS_NEW           150
    NBPRMENS             NBPRMENS_NEW               735
    NBPRCUM              NBPRCUM_NEW                138
    NBPRETBMENS          NBPRETBMENS_NEW            239
    NBPRETBCUM           NBPRETBCUM_NEW             735
    NBPRFLIBMENS         NBPRFLIBMENS_NEW           838
    
    30 rows selected.
    
    SQL>
    Perfect. This works well

    Thanks a lot...:-)

    However, in case the second value (Code_Indc_New) was completely unrelated to CODE_INDC, is there a solution? Can we unpivot one column and associate multiple columns containing hard coded values to them?

  10. #10
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down

    Quote Originally Posted by S. BASU View Post
    Perfect. This works well

    Thanks a lot...:-)

    However, in case the second value (Code_Indc_New) was completely unrelated to CODE_INDC, is there a solution? Can we unpivot one column and associate multiple columns containing hard coded values to them?
    In order to associate multiple columns containing hard coded values to an unpivoted column, you need some rule to know what hard coded value you want to associate to what column value.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  11. #11
    Join Date
    Apr 2012
    Posts
    32

    Exclamation

    Something like this:

    Code:
    CODE_INDC            CODE_INDC_NEW        VALR_INDC
    -------------------- -------------------- ---------
    AAA                     ZZZ                           198
    BBB                      YYY                           788
    CCC                     XXX                           138
    DDD                     WWW                         378
    EEE                      VVV                           838
    EEE                      UUU                           108
    FFF                      TTT                           853
    GGG                     SSS                            239
    HHH                     RRR                            1,002
    .....
    basically there's no relation between the two columns containing hard coded values, thus a concatenation of string with one column doesn't give the second column

  12. #12
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down

    Quote Originally Posted by S. BASU View Post
    . . .
    basically there's no relation between the two columns containing hard coded values, thus a concatenation of string with one column doesn't give the second column
    If there is no relation between the two columns, then what generates/produces the value of the "hard-coded" columns? random values?
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  13. #13
    Join Date
    Apr 2012
    Posts
    32
    Quote Originally Posted by LKBrwn_DBA View Post
    If there is no relation between the two columns, then what generates/produces the value of the "hard-coded" columns? random values?
    Yes. the hard coded values are as the name suggests, codes which may change over time. When this changes I'd need to modify the SQL script.

    So today for different values the codes may be AAA, BBB, CCC for one column and XXX, YYY, ZZZ for the other column. This may very well change.

    For the moment thus we have to consider them as random values. Of course they have functional significance but that's not for me the developer to delve into.

  14. #14
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713
    Quote Originally Posted by S. BASU View Post
    Yes. . . . we have to consider them as random values. Of course they have functional significance but that's not for me the developer to delve into.
    Random it is:
    Code:
    SQL> WITH T (libl_sour,mont_1,mont_2,mont_3,mont_4,mont_5)
      2    AS (
      3      SELECT 'PROGPACT',198,788,138,378,838 FROM DUAL UNION ALL
      4      SELECT 'PROGPACT',108,853,239,1002,154 FROM DUAL UNION ALL
      5      SELECT 'PROGPACT',573,150,735,198,788 FROM DUAL UNION ALL
      6      SELECT 'PROGPACT',138,378,838,108,853 FROM DUAL UNION ALL
      7      SELECT 'PROGPACT',239,1002,154,573,150 FROM DUAL UNION ALL
      8      SELECT 'PROGPACT',735,138,239,735,838 FROM DUAL)
      9  SELECT libl_sour,Code_Indc
     10       , 'NEW_' || DBMS_RANDOM.String ( 'X', 10 ) Random_Code
     11       , Valr_Indc
     12    FROM T UNPIVOT INCLUDE NULLS (Valr_Indc
     13           FOR Code_Indc
     14           IN  (Mont_1 AS 'NBPRMENS'
     15             , Mont_2 AS 'NBPRCUM'
     16             , Mont_3 AS 'NBPRETBMENS'
     17             , Mont_4 AS 'NBPRETBCUM'
     18             , Mont_5 AS 'NBPRFLIBMENS'
     19              )                 )
     20  /
    
    LIBL_SOUR            CODE_INDC            RANDOM_CODE          VALR_INDC
    -------------------- -------------------- -------------------- ---------
    PROGPACT             NBPRMENS             NEW_V8103AJCHI             198
    PROGPACT             NBPRCUM              NEW_K2APFXEH39             788
    PROGPACT             NBPRETBMENS          NEW_K8I1FXPQJZ             138
    PROGPACT             NBPRETBCUM           NEW_1TZ0WHHS8X             378
    PROGPACT             NBPRFLIBMENS         NEW_74R13YV8O0             838
    PROGPACT             NBPRMENS             NEW_GZF76A5QRN             108
    PROGPACT             NBPRCUM              NEW_AL5HIHJRIN             853
    PROGPACT             NBPRETBMENS          NEW_UMLUBN6D94             239
    PROGPACT             NBPRETBCUM           NEW_Y5PY7745RN           1,002
    PROGPACT             NBPRFLIBMENS         NEW_4RDEEQLBSL             154
    PROGPACT             NBPRMENS             NEW_MPUAVRMLLF             573
    PROGPACT             NBPRCUM              NEW_E4WGLHS9M9             150
    PROGPACT             NBPRETBMENS          NEW_5OIV29D754             735
    PROGPACT             NBPRETBCUM           NEW_8T34ETXCKT             198
    PROGPACT             NBPRFLIBMENS         NEW_NI9N4Q85O1             788
    PROGPACT             NBPRMENS             NEW_0RZBGOY7I1             138
    PROGPACT             NBPRCUM              NEW_VUFPJTXUS6             378
    PROGPACT             NBPRETBMENS          NEW_AFSWXAER1Q             838
    PROGPACT             NBPRETBCUM           NEW_ADZ5FPH2BW             108
    PROGPACT             NBPRFLIBMENS         NEW_US7ZI7C7IX             853
    PROGPACT             NBPRMENS             NEW_WJLHXY1E3I             239
    PROGPACT             NBPRCUM              NEW_F9KH6BKXRV           1,002
    PROGPACT             NBPRETBMENS          NEW_QSMZ5TM43F             154
    PROGPACT             NBPRETBCUM           NEW_7BBM78B1JJ             573
    PROGPACT             NBPRFLIBMENS         NEW_7XCWE14RWA             150
    PROGPACT             NBPRMENS             NEW_22Z4X2D7L1             735
    PROGPACT             NBPRCUM              NEW_D9QE4NHWIQ             138
    PROGPACT             NBPRETBMENS          NEW_USU4S4URR2             239
    PROGPACT             NBPRETBCUM           NEW_3P73K5EOTT             735
    PROGPACT             NBPRFLIBMENS         NEW_APA9YCKTSW             838
    
    30 rows selected.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  15. #15
    Join Date
    Apr 2012
    Posts
    32
    Quote Originally Posted by LKBrwn_DBA View Post
    Random it is:
    Code:
    SQL> WITH T (libl_sour,mont_1,mont_2,mont_3,mont_4,mont_5)
      2    AS (
      3      SELECT 'PROGPACT',198,788,138,378,838 FROM DUAL UNION ALL
      4      SELECT 'PROGPACT',108,853,239,1002,154 FROM DUAL UNION ALL
      5      SELECT 'PROGPACT',573,150,735,198,788 FROM DUAL UNION ALL
      6      SELECT 'PROGPACT',138,378,838,108,853 FROM DUAL UNION ALL
      7      SELECT 'PROGPACT',239,1002,154,573,150 FROM DUAL UNION ALL
      8      SELECT 'PROGPACT',735,138,239,735,838 FROM DUAL)
      9  SELECT libl_sour,Code_Indc
     10       , 'NEW_' || DBMS_RANDOM.String ( 'X', 10 ) Random_Code
     11       , Valr_Indc
     12    FROM T UNPIVOT INCLUDE NULLS (Valr_Indc
     13           FOR Code_Indc
     14           IN  (Mont_1 AS 'NBPRMENS'
     15             , Mont_2 AS 'NBPRCUM'
     16             , Mont_3 AS 'NBPRETBMENS'
     17             , Mont_4 AS 'NBPRETBCUM'
     18             , Mont_5 AS 'NBPRFLIBMENS'
     19              )                 )
     20  /
    
    LIBL_SOUR            CODE_INDC            RANDOM_CODE          VALR_INDC
    -------------------- -------------------- -------------------- ---------
    PROGPACT             NBPRMENS             NEW_V8103AJCHI             198
    PROGPACT             NBPRCUM              NEW_K2APFXEH39             788
    PROGPACT             NBPRETBMENS          NEW_K8I1FXPQJZ             138
    PROGPACT             NBPRETBCUM           NEW_1TZ0WHHS8X             378
    PROGPACT             NBPRFLIBMENS         NEW_74R13YV8O0             838
    PROGPACT             NBPRMENS             NEW_GZF76A5QRN             108
    PROGPACT             NBPRCUM              NEW_AL5HIHJRIN             853
    PROGPACT             NBPRETBMENS          NEW_UMLUBN6D94             239
    PROGPACT             NBPRETBCUM           NEW_Y5PY7745RN           1,002
    PROGPACT             NBPRFLIBMENS         NEW_4RDEEQLBSL             154
    PROGPACT             NBPRMENS             NEW_MPUAVRMLLF             573
    PROGPACT             NBPRCUM              NEW_E4WGLHS9M9             150
    PROGPACT             NBPRETBMENS          NEW_5OIV29D754             735
    PROGPACT             NBPRETBCUM           NEW_8T34ETXCKT             198
    PROGPACT             NBPRFLIBMENS         NEW_NI9N4Q85O1             788
    PROGPACT             NBPRMENS             NEW_0RZBGOY7I1             138
    PROGPACT             NBPRCUM              NEW_VUFPJTXUS6             378
    PROGPACT             NBPRETBMENS          NEW_AFSWXAER1Q             838
    PROGPACT             NBPRETBCUM           NEW_ADZ5FPH2BW             108
    PROGPACT             NBPRFLIBMENS         NEW_US7ZI7C7IX             853
    PROGPACT             NBPRMENS             NEW_WJLHXY1E3I             239
    PROGPACT             NBPRCUM              NEW_F9KH6BKXRV           1,002
    PROGPACT             NBPRETBMENS          NEW_QSMZ5TM43F             154
    PROGPACT             NBPRETBCUM           NEW_7BBM78B1JJ             573
    PROGPACT             NBPRFLIBMENS         NEW_7XCWE14RWA             150
    PROGPACT             NBPRMENS             NEW_22Z4X2D7L1             735
    PROGPACT             NBPRCUM              NEW_D9QE4NHWIQ             138
    PROGPACT             NBPRETBMENS          NEW_USU4S4URR2             239
    PROGPACT             NBPRETBCUM           NEW_3P73K5EOTT             735
    PROGPACT             NBPRFLIBMENS         NEW_APA9YCKTSW             838
    
    30 rows selected.
    Hello LKBrwn_DBA

    Thanks for that.

    I believe I didn't explain clearly though. When I meant random I didn't mean randomly generated by Oracle. I meant randomly specified / given by my client.

    So if its something like:

    Code:
    CODE_INDC            CODE_INDC_NEW        VALR_INDC
    -------------------- -------------------- ---------
    AAA                     ZZZ                           198
    BBB                      YYY                           788
    CCC                     XXX                           138
    DDD                     WWW                         378
    EEE                      VVV                           838
    EEE                      UUU                           108
    FFF                      TTT                           853
    GGG                     SSS                            239
    HHH                     RRR                            1,002
    .....
    There's basically there's no relation between the two columns containing hard coded values.

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
  •