11 Replies Latest reply: Oct 12, 2012 2:18 AM by BluShadow RSS

    column name with column value in sql

    Tsk
      Hi

      I need to get the data from the table as below format. i.e column name and column value.
      Ex : if a table has two rows and five columns then my query should return 10 rows as column name and column value.

      Table : Quest
         Sno          Name        Place
      -------------------------------------
      1            aa           Mumbai
      2            bb           Delhi
      
      My result should be in below format
      
         column_name              value
      --------------------------------------
             Sno                  1
             Name                 aa
             Place                Mumbai
             Sno                  2
             Name                 bb
             Place                Delhi
        • 1. Re: column name with column value in sql
          jeneesh
          select col,val
          from(select 'Sno' col,sno val,sno d,1 rn
          from Quest
          union all
          select 'Name',name,sno d,2
          from Quest
          union all
          select 'Place',Place,sno d,3
          from Quest)
          order by d,rn
          • 2. Re: column name with column value in sql
            803439
            to_char(sno) should be there in the first inner query other wise query will fail
            SELECT COL,VAL
            from(select 'Sno' col,to_char(sno) val,sno d,1 rn
            from NSK_TEST_1
            union all
            select 'Name',name,sno d,2
            from NSK_TEST_1
            union all
            select 'Place',Place,sno d,3
            FROM NSK_TEST_1)
            order by d,rn
            • 3. Re: column name with column value in sql
              Tsk
              above table is sample table. i need to get the data dynamically based on table name i used in the query. so i can't hard code the column names.
              • 4. Re: column name with column value in sql
                jeneesh
                Sun Vth Oracle wrote:
                to_char(sno) should be there in the first inner query other wise query will fail
                If sno is number, right?

                Anyhow, the code is not tested - just giving him a hint how it can be done..
                • 5. Re: column name with column value in sql
                  jeneesh
                  Tsk wrote:
                  above table is sample table. i need to get the data dynamically based on table name i used in the query. so i can't hard code the column names.
                  Then you generate the select statment dynamically ..

                  What we have shown is also sample....
                  • 6. Re: column name with column value in sql
                    Marwim
                    {message:id=9360005}
                    • 7. Re: column name with column value in sql
                      803439
                      CREATE TABLE NSK_TEST_1 (SNO NUMBER, NAME VARCHAR2(10),        PLACE VARCHAR2(10));
                      INSERT INTO NSK_TEST_1 VALUES (1,'aa',  'Mumbai');
                      INSERT INTO NSK_TEST_1 VALUES (2,'bb','Delhi');
                      INSERT INTO NSK_TEST_1 VALUES (3,'cc','Hyd');
                      commit;
                      
                      *Query:*
                      
                      SELECT N2.COLUMN_NAME,DECODE(N2.COLUMN_ID,1,TO_CHAR(N1.SNO),2,N1.NAME,3,N1.PLACE) REQ_VAL 
                      FROM NSK_TEST_1 N1,(
                      SELECT COLUMN_NAME, COLUMN_ID FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'NSK_TEST_1')N2
                      ORDER BY N1.SNO,N2.COLUMN_ID;
                      
                      
                      is giving output as required, output is below
                      
                      SNO     1
                      NAME     aa
                      PLACE     Mumbai
                      SNO        2
                      NAME     bb
                      PLACE     Delhi
                      SNO         3
                      NAME     cc
                      PLACE     Hyd
                      
                       then I have done below:
                      
                      
                      alter table nsk_test_1 add test_4 varchar2(10) default 'NSK';
                      
                      
                      I have used the above same query
                      
                      SELECT N2.COLUMN_NAME,N2.COLUMN_ID,N1.SNO,N1.NAME,N1.PLACE,decode(n2.column_id,1,to_char(n1.sno),2,n1.name,3,n1.place) FROM NSK_TEST_1 N1,(
                      SELECT COLUMN_NAME, COLUMN_ID FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'NSK_TEST_1')N2
                      order by n1.sno,n2.column_id;
                      
                      output as below
                      
                      SNO        1
                      NAME     aa
                      PLACE     Mumbai
                      TEST_4     (null)
                      SNO        2
                      NAME     bb
                      PLACE     Delhi
                      TEST_4     (null)
                      SNO         3
                      NAME     cc
                      PLACE     Hyd
                      TEST_4     (null)
                      
                      in the newly added column is comming as null, but the row is coming, may be any one can help in improving the same.
                      • 8. Re: column name with column value in sql
                        RamaKrishna.CH
                        Try this:

                        SELECT VALUE(t).getrootelement() "COlumn name" ,DECODE(VALUE(t).getrootelement()
                        ,'ENAME',EXTRACTVALUE(VALUE(t),'//ENAME')
                        ,'SAL',EXTRACTVALUE(VALUE(t),'//SAL') ) "VALUES"
                        FROM TABLE(XMLSEQUENCE((SELECT xmlagg(XMLFOREST(ENAME,SAL)) FROM EMP))) t
                        • 9. Re: column name with column value in sql
                          Ashu_Neo
                          See you are using 11g, Then you can use UNPIVOT. Check this.
                          SQL> ed
                          Wrote file afiedt.buf
                          
                            1  WITH data1 AS
                            2  (
                            3  SELECT '1' "Sno", 'aa' "Name", 'Mumbai' "Place" from dual union all
                            4  SELECT '2','bb','Delhi' from dual
                            5  )
                            6  Select * from data1
                            7  UNPIVOT
                            8  (
                            9    value
                           10    FOR column_name IN ("Sno","Name","Place")
                           11* )
                          SQL> /
                          
                          COLUM VALUE
                          ----- ------
                          Sno   1
                          Name  aa
                          Place Mumbai
                          Sno   2
                          Name  bb
                          Place Delhi
                          
                          6 rows selected.
                          Add on:-
                          SELECT '1' "Sno", 'aa' "Name", 'Mumbai' "Place"
                          Above, data needs to same datatype else UNPIVOT doesn't work. I am not sure fully. But you can explore more.. :) :)

                          Thanks!
                          Ashutosh

                          Edited by: Ashu_Neo on Oct 12, 2012 12:47 PM
                          • 10. Re: column name with column value in sql
                            BluShadow
                            Take a read of this article thread: {thread:id=2309172}
                            which includes code that dynamically processes a query to output the column names and retrieve the data.

                            With a little bit of adaptation it can produce the output in the style you want.
                            • 11. Re: column name with column value in sql
                              Billy~Verreynne
                              Tsk wrote:

                              I need to get the data from the table as below format. i.e column name and column value.
                              Ex : if a table has two rows and five columns then my query should return 10 rows as column name and column value.
                              One method for data transformation in Oracle, is pipelined table functions. The basic approach is as follows:
                              SQL> create or replace type TNameValue is object(
                                2          col_name        varchar2(30),
                                3          col_value       varchar2(4000)
                                4  );
                                5  /
                              
                              Type created.
                              
                              SQL> 
                              SQL> create or replace type TNameValueArray is table of TNameValue;
                                2  /
                              
                              Type created.
                              
                              SQL> 
                              SQL> create or replace function NameValues( sqlSelect varchar2 ) return TNameValueArray pipelined is
                                2          c       integer;
                                3          colCnt  integer;
                                4          colList DBMS_SQL.DESC_TAB3;
                                5          rc      number;
                                6          colVal  varchar2(4000);
                                7  begin
                                8          c := DBMS_SQL.open_cursor;
                                9          DBMS_SQL.parse( c, sqlSelect, DBMS_SQL.Native );
                               10  
                               11          DBMS_SQL.describe_columns3( c, colCnt, colList );
                               12  
                               13          for i in 1..colCnt loop
                               14                  DBMS_SQL.define_column( c, i, colVal, 4000 );
                               15          end loop;
                               16  
                               17          rc := DBMS_SQL.execute_and_fetch( c );
                               18  
                               19          loop
                               20                  for i in 1..colCnt loop
                               21                          DBMS_SQL.column_value( c, i, colVal );
                               22                          pipe row(  TNameValue( colList(i).col_name, colVal ) );
                               23                  end loop;
                               24                  exit when DBMS_SQL.fetch_rows( c ) = 0;
                               25          end loop;
                               26  
                               27          DBMS_SQL.Close_Cursor( c );
                               28  end;
                               29  /
                              
                              Function created.
                              
                              SQL> 
                              SQL> select * from TABLE( NameValues('select rownum, empno, ename from emp where rownum < 4 order by 1') );
                              
                              COL_NAME   COL_VALUE
                              ---------- ----------
                              ROWNUM     1
                              EMPNO      7369
                              ENAME      SMITH
                              ROWNUM     2
                              EMPNO      7499
                              ENAME      ALLEN
                              ROWNUM     3
                              EMPNO      7521
                              ENAME      WARD
                              The problem with this transformation approach is that different column value types (date, number, clob, raw, blob, etc) have to be displayed as string - as the SQL projection of this transformation is limited to a single data type for displaying the underlying data.

                              The above code makes use of implicit data type conversion of non-string values to string values. And this is not always the best of ideas.