How a row data is converted into a column data

703280

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

BluShadow

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

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

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

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

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

I like women, not sure what your problem is.