CODEWITHSUNDEEP
sqloracle-database
MKS
MKS

Reputation: 59

column to row transformation

I have following table names ETEST with col1 and col2 as column names. 

  col1  col2
  ====    ==== 
  eid   101
  name  abc
  age   30
  eid   102
  name  xyz
  age   40

The values eid, name and can be repeated,I want to write an sql query to display data like below:

eid name  age
101 abc   30
102  xyz  40

I tried something like below but that's not works for all the records.

 SELECT MAX(DECODE(a.col1,'eid',a.col2 )) eid,
  MAX(DECODE(a.col1,'name',a.col2 )) name ,
  MAX(DECODE(a.col1,'age',a.col2 )) age
FROM
  (SELECT rownum,
    last_value(col1 ignore nulls) over (order by rownum) col1,
    last_value(col2 ignore nulls) over (order by rownum) col2
  FROM etest
  ORDER BY rownum
  )a;

Please note , there can be n number of entries for eid,name and age in col1 of the above table.

Please help. Regards, MKS

Upvotes: 0

Views: 76

Answers (1)

ridi
ridi

Reputation: 140

The most elegant way of rewriting columns to rows is making use of the pivot statement. Such a code would look like something like this:

select *
  from etest
  pivot
   ( min(col2)
     for col1 in ('eid', 'name', 'age')
   )

But I agree with the former statement: the database design is doubtfull. You can never be sure that the choosen eid in one row belongs to another name or age in another row. So this solution offered is just a halfway one.

Upvotes: 1

Related Questions