1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Dynamic generation column name

Discussion in 'SQL PL/SQL' started by mabyna, Feb 27, 2014.

  1. mabyna

    mabyna Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    Hi all,

    Can you please help me, how to generate dynamic column names? I prepared the query for that, but one little thing missing: How to enforce to interpret 'X'||rn as column name, not only as a string. Have you got please any solution how to do that?

    As output I would like to have:


    PROJECT_NUMBER____VARIETY
    ----------------------------------------
    1 _____________________a1
    1 _____________________a2
    1 _____________________a3
    2 _____________________a4
    2 _____________________a5
    2 _____________________a6


    Code (SQL):
    CREATE TABLE TABLE_NAME
    (
      PROJECT_number    VARCHAR2(25 BYTE),
      X1                VARCHAR2(20 BYTE),
      X2                VARCHAR2(20 BYTE),
      X3                VARCHAR2(20 BYTE)
    );

    INSERT INTO TABLE_NAME (PROJECT_number, X1, X2, X3)
    VALUES ('1','a1', 'a2', 'a3');

    INSERT INTO TABLE_NAME (PROJECT_number, X1, X2, X3)
    VALUES ('2','a4', 'a5', 'a6');


    WITH nums AS (
    SELECT rownum rn FROM (SELECT 1,2 FROM dual GROUP BY cube (1, 2)) WHERE rownum <= 3)

    SELECT
            PROJECT_number,
           'X'||rn "variety"
    FROM TABLE_NAME, nums
    ORDER BY PROJECT_number;
    Thank you for your suggestions.
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    One variant : for oracle version >= 11g
    Code (SQL):


    SELECT * FROM TABLE_NAME
    unpivot (variety FOR x IN (x1,x2,x3));

    SQL>

    PROJECT_NUMBER            X  VARIETY            
    ------------------------- -- --------------------
    1                         X1 a1                  
    1                         X2 a2                  
    1                         X3 a3                  
    2                         X1 a4                  
    2                         X2 a5                  
    2                         X3 a6                  

     6 ROWS selected

     

    for 10g : (original text in https://community.oracle.com/thread/1042955)

    Code (SQL):

    DEFINE     TABLE_NAME     = TABLE_NAME
    DEFINE     key_column     = PROJECT_NUMBER

    COLUMN     case_col     NEW_VALUE     case_txt
    COLUMN     with_col     NEW_VALUE     with_txt


    WITH     got_r_num     AS
    (
         SELECT     column_name
         ,     data_type
         ,     ROW_NUMBER () OVER (ORDER BY column_id)     AS r_num
         FROM     user_tab_columns
         WHERE    TABLE_NAME     = '&table_name'
         AND     column_name     != '&key_column'
    )
    SELECT     REPLACE ( SUBSTR ( SYS_CONNECT_BY_PATH ( 'SELECT ''' || column_name || '''     AS name FROM dual' -- That's a <TAB> before AS
                                        , ' UNION ALL '
                                    )
                    , 12
                    )
              , ' UNION ALL '
              , ' UNION ALL' || CHR (10)
              ) AS with_col
    ,     SUBSTR ( REPLACE ( SYS_CONNECT_BY_PATH ( column_name || '''     THEN '
                                                || CASE
                                                 WHEN  data_type = 'NUMBER'
                                                 THEN  'TO_CHAR (' || column_name
                                                                          || ')'
                                                 WHEN  data_type = 'DATE'
                                                 THEN  'TO_CHAR (' || column_name
                                                                          || ', ''DD-Mon-YYYY HH24:MI:SS'')'
                                                 ELSE  column_name
                                            END
                                            , 'WHEN '''
                                        )
                    , 'WHEN '''
                    , CHR (10) || 'WHEN '''
                    )
              , 2
              ) AS case_col
    FROM     got_r_num
    WHERE     CONNECT_BY_ISLEAF     = 1
    START WITH     r_num     = 1
    CONNECT BY     r_num     = 1 + PRIOR r_num
    ;



    WITH     column_names      AS
    (
         &with_txt
    )
    SELECT     t.&key_column
    ,     c.name
    ,     CASE     c.name
              &case_txt
         END     AS val
    FROM          column_names               c
    CROSS JOIN    &TABLE_NAME     t
    ORDER BY     t.&key_column
    ;

    SQL >

    PROJECT_NUMBER            NAME VAL                
    ------------------------- ---- --------------------
    1                         X1   a1                  
    1                         X2   a2                  
    1                         X3   a3                  
    2                         X2   a5                  
    2                         X3   a6                  
    2                         X1   a4                  

     6 ROWS selected