Lets say my table like this and the table name is Employee.

Employee ID         Name               Age
   1                Dilshan             22
   2                Udara               25
   3                Malith              45
   4                Dilshan             40

select Name,Age from Employee
group by Name,Age;

the results will be like this.

Dilshan             22
Dilshan             40
Udara               25
Malith              45

what i want is to have a separate columns for to show all ages per employee name. My intended results are like this. I'm using oracle 11g.

Name               Age1     Age2
Dilshan             22       40
Udara               25       null
Malith              45       null
  • Does the output Name="Dilshan"; Age="22,40" is safe? If not - does max amount of different ages for separate name exists?
    – Akina
    Commented Sep 11, 2019 at 8:21
  • Actually i tried listagg and i was able to get ""Name="Dilshan"; Age="22,40""". But that is not i want. Commented Sep 11, 2019 at 8:26
  • How do you define AGE1 and AGE2? Why is AGE1 not '40'? Commented Sep 11, 2019 at 9:25
  • That's what i'm confusing. Actually this came as a requirement. Let's say Age1 and Age2 are fixed. Commented Sep 11, 2019 at 9:35
  • You want a dynamic PIVOT because you don't know how many employees called Dilshan there will be! However, I'm more curious about why you want this strange result structure? You could have 10 Dilshans? What is your ultimate goal? What happens if two Dilshans have the same age in years?
    – Vérace
    Commented Sep 11, 2019 at 10:46

1 Answer 1


Try this one

   with cte1 as (
   select min(id) as id
   from Employee e
   group by name

   , cte2 as (
   select e.name, e.Age as Age1, eo.Age as age2, e.id
   from Employee e
   left join Employee eo on e.name = eo.name
   and e.ID <> eo.ID

   select cte2.name, cte2.Age1, cte2.age2
   from cte1
   join cte2 on cte1.id =cte2.ID

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.