This discussion is archived
11 Replies Latest reply: Oct 12, 2012 12:18 AM by BluShadow RSS

column name with column value in sql

Tsk Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    {message:id=9360005}
  • 7. Re: column name with column value in sql
    803439 Explorer
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points