3 Replies Latest reply on Jul 24, 2013 7:34 PM by user626688

    Fetching values based on column names

    user626688

      Hi,

       

      I have three tables. Table A table contains a column with all the column_names of table B. Table B is having a emp_id and some other fields which are stored as values in column of TABLE A.  I have to fetch the emp_id and column value from table B based on the data from Table A. and store it in another table.

       

      Example:

       

      Table A -col1

       

      Emp_name

      Emp_sal

      Emp_dept

       

      Table B

      Emp_id   Emp_name     Emp_sal   Emp_dept

      1001                   ABC    1000         Sales

      1002                    XYZ    2000         SVCs

       

      TABLE C

      Emp_id          Emp_details          Value

      1001               Emp_name          ABC

      1001                Emp_sal             1000

      1001               Emp_dept            Sales

      1002               Emp_name          XYZ

      1002               Emp_sal               2000

      1002               Emp_dept             SVCs      

       

      Using the list from TABLE A, I have to select each Id and and corresponding value from TABLE B and insert the same into TABLE C. I know that we can use a for loop but i have trouble in selecting the column name based on the cursor value; Can someone help?

       

       

       

       

       

       

       

       


        • 1. Re: Fetching values based on column names
          Frank Kulash

          Hi,

           

          It looks like you want to do 2 rather tricky things:

          (1) Unpivot the data from multiple columns into 1 value column

          (2) Get the column names for the unpivot from another table

           

          Let's start by how you might do just part 1, unpivoting the data:

           

          INSERT INTO table_c (emp_id, emp_details, value)

          WITH all_varchar2   AS

          (

              SELECT  emp_id

              ,       emp_name

              ,       TO_CHAR (emp_sal) AS emp_sal

              ,       emp_dept

              ,       ...        -- Include all coloumns you might need

              FROM    table_b

          )

          SELECT  emp_id, emp_details, value

          FROM    all_varchar2

          UNPIVOT (    value

                  FOR  emp_details IN

                       (    emp_name     -- Dynamic

                       ,    emp_sal      -- Dynamic

                       ,    emp_dept     -- Dynamic

                       )

                  )

          ;

           

          Since value is one column, everything going into that column has to have the same (or a very similar) datatype.  The sub-query all_varchar2 converts all the columns that you might want to pivot into VARCHAR2s (if they're not already VARCHAR2s) so they are ready to pivot.  You might consider making a permanent view for this.

          Now, instead of hard-colding the 3 line marked "Dynamic", you want to fetch them from table_a.  You won't know ahead of time what those columns are, and you won't even know how many columns there will be. You need dynamic SQL to write that part of the INSERT statement for you at run time.

          Here's one way to do it in SQL*Plus and SQL:

           

          -- Preliminary Query

           

          COLUMN col_list_col NEW_VALUE col_list

           

          SELECT  LISTAGG (col1, ', ') WITHIN GROUP (ORDER BY col1)

                      AS col_list_col
          FROM    table_a;

           

           

          -- Main (Dynamic) Statement:


          INSERT INTO table_c (emp_id, emp_details, value)
          WITH all_varchar2   AS
          (
               SELECT  emp_id
              ,       emp_name
              ,       TO_CHAR (emp_sal)    AS emp_sal
              ,       emp_dept
              ,       ...      -- Include all coloumns you might need
              FROM    table_b
          )
          SELECT  emp_id, emp_details, value
          FROM    all_varchar2
          UNPIVOT (    value
                  FOR  emp_details IN
                       (    &col_list
                       )
                  )
          ;

           

          Dynamic SQL often works like this.  First, you do a preliminary query to write the part that isn't know before run-time.  Then you run the main statement, that has the results of the preliminary query built into it.

          If you're doing this in PL/SQL, you'll use a regular string variable for col_list, instead of a substitution variable.

          • 2. Re: Fetching values based on column names
            davidp 2

            You need to build a pretty dynamic query:

            insert into TABLE_c (Emp_id, Emp_details, Value)

            select emp_id

                  , a.col1

                  , CASE upper(a.col1) when 'EMP_NAME' then to_char(emp_name), WHEN 'EMP_SAL' then to_char(emp_sal) WHEN 'EMP_DEPT' then to_char(emp_dept) END

            from a, b

            Generate the CASE statement from a query on A. Assemble it all into a string and EXECUTE IMMEDIATE it.

             

             

            Now I see Frank's reply, I like it a lot. I have hardly used UNPIVOT.

            You might want to preserve the case of the column identifers with

            for emp_details in (Emp_name as 'Emp_name', Emp_sal as 'Emp_sal', Emp_dept as 'Emp_dept')

            1 person found this helpful
            • 3. Re: Fetching values based on column names
              user626688

              Great Guys! Thanks Frank and David!