Column values to header oracle

Question

I have a table with schema as below

Empid   Field Type  Field Value
123         Name          John
123         Age            33
124         Name          Tijo
124         Age            24

Output should be in followinf format

Empid       Name             Age
123         John             33
124         Tijo             24

How can i implement this using query from oracle database?

Answer 1

This type of transformation of rows into columns is known as a PIVOT. There are several ways that this can be done.

Since you are using Oracle 11g, you can use the PIVOT function:

select empid, Name, age
from
(
  select empid,
    fieldtype,
    fieldvalue
  from yt
) 
pivot
(
  max(fieldvalue)
  for fieldtype in ('Name' as Name, 'Age' as Age)
);

See SQL Fiddle with Demo.

Prior to Oracle 11g, you could use an aggregate function with a CASE expression:

select empid,
  max(case when fieldtype = 'Name' then fieldvalue end) name,
  max(case when fieldtype = 'Age' then fieldvalue end) age
from yt
group by empid;

See SQL Fiddle with Demo.

You can also get the result by joining on the table multiple times:

select t1.empid,
  t1.fieldvalue name,
  t2.fieldvalue age
from yt t1
left join yt t2
  on t1.empid = t2.empid
  and t2.fieldtype = 'Age'
where t1.fieldtype = 'Name';

See SQL Fiddle with Demo. Each version gives the result:

| EMPID | NAME | AGE |
----------------------
|   123 | John |  33 |
|   124 | Tijo |  24 |

Answer 2

Sounds like you are trying to PIVOT your table. One option is to use MAX with CASE:

select empid,
   max(case when fieldtype = 'Name' then fieldvalue end) Name,
   max(case when fieldtype = 'Age' then fieldvalue end) Age
from yourtable
group by empid

SQL Fiddle Demo