Forum Stats

  • 3,825,238 Users
  • 2,260,485 Discussions
  • 7,896,462 Comments

Discussions

How a row data is converted into a column data

703280
703280 Member Posts: 2
edited Jan 19, 2010 7:10AM in SQL & PL/SQL
How the data in any row of a table is converted into a column data. (the query to do that)
e.g.

id name city
a11 Ram Kolhapur
b22 Manu Satara

the result should be

a11 b22
ram Manu
kolhapur Satara

Is it possible , if yes how ? boz this has been asked in an interview?

Thanks
Prithviraj
Tagged:

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 41,978 Red Diamond
    user10643728 wrote:
    How the data in any row of a table is converted into a column data. (the query to do that)
    e.g.

    id name city
    a11 Ram Kolhapur
    b22 Manu Satara

    the result should be

    a11 b22
    ram Manu
    kolhapur Satara

    Is it possible , if yes how ? boz this has been asked in an interview?

    Thanks
    Prithviraj
    This is called a Pivot.

    In 11g there is specifically a PIVOT keyword for use in SQL. In 10g or below there are various techniques using decode or case statements etc. to achieve the same.
    However, when pivoting data it is necessary to know how many columns (or the maximum expected columns) will be produced by the query. You can't easily have it completely dynamic to pivot any number of rows to any number of columns.

    Ideally, it should the the front end (client) reporting tool that does this sort of work as Report writers are generally very good at pivoting data and formatting it to fit on report pages, and they often have the ability to deal with a dynamic number of resultant columns, unlike SQL.
  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    edited Jan 18, 2010 7:00AM
    If someone ask stuff like this, i will ask them why they want me to do such crazy thing. Pivot is a presentation layer work. Why do they ask DB guy to do it :(

    But any way here you go.

    with t
    as
    (
    select 'a11' id, 'Ram' name, 'Kolhapur' city from dual
    union all
    select 'b22', 'Manu', 'Satara' from dual
    )
    select max(decode(rno,1,decode(rno1,1,id,2,name,3,city))), 
           max(decode(rno,2,decode(rno1,1,id,2,name,3,city)))
      from (select rownum rno, id, name, city 
              from t)
      cross join (select level rno1 from dual connect by level <= 3)
     group by rno1
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    I like Pivot and UnPivot B-)
    create table pivUnPiv(id,name,city) as
    select 'a11','Ram' ,'Kolhapur' from dual union all
    select 'b22','Manu','Satara'   from dual;
    
    select *
      from (select Row_Number() over(order by id) as rn,
            id,name,city from pivUnPiv)
    unpivot(Vals for Base in(ID,NAME,CITY))
    Pivot(max(Vals) for rn in(1 as Col1,
                              2 as Col2))
    order by decode(Base,'ID',1,'NAME',2,3);
    
    BASE  COL1      COL2
    ----  --------  ------
    ID    a11       b22
    NAME  Ram       Manu
    CITY  Kolhapur  Satara
    evaluated order of Pivot and UnPivot in select statement
    995121
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    I like model clause ,too :8}
    select Col1,Col2
      from pivUnPiv
     model
    dimension by(Row_Number() over(order by id) as soeji)
    measures(id,name,city,
             cast(null as varchar2(10)) as COL1,
             cast(null as varchar2(10)) as COL2)
    rules(
    Col1[1] = ID[1],   Col2[1] = ID[2],
    Col1[2] = name[1], Col2[2] = name[2],
    Col1[3] = city[1], Col2[3] = city[2])
    order by soeji;
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    I like sys.odciNumberList ;-)
    select
    case b.Column_Value
    when 1 then max(decode(a.rn,1,a.ID))
    when 2 then max(decode(a.rn,1,a.name))
    when 3 then max(decode(a.rn,1,a.city)) end as COL1,
    case b.Column_Value
    when 1 then max(decode(a.rn,2,a.ID))
    when 2 then max(decode(a.rn,2,a.name))
    when 3 then max(decode(a.rn,2,a.city)) end as COL2
      from (select Row_Number() over(order by id) as rn,
            id,name,city from pivUnPiv) a,Table(sys.odciNumberList(1,2,3)) b
    group by b.Column_Value
    order by b.Column_Value;
  • BluShadow
    BluShadow Member, Moderator Posts: 41,978 Red Diamond
    I like women, not sure what your problem is. ;)
This discussion has been closed.