Trying to convert rows to columns

Question

I have data like below trying to convert rows into columns but getting null values and extra rows

 ID  Vechile_type   Vechile
 1     car           maruti
 1     car            honda
 1     bike          suzki
 1     bike           hero

Need output as below

 ID    Car    Bike
 1     Maruti  SuZki
 1      Honda   hero

when i tried to use case condition getting null values like below

  ID    Car    Bike
   1      Maruti  NULL
   1      NULL  hero
   1     Honda  NULL

not able to use pivot with aggregations. is there any alternative way?

Answer 1

One option would be pivoting through use of row_number() analytic function :

select id, car, bike 
  from ( select row_number() over (partition by Vehicle_type order by 1) as rn, 
                t.* 
           from tab t )
 pivot 
( max(Vehicle) for Vehicle_type in ('car' as car,'bike' as bike) )
 order by rn;

Demo

Answer 2

Here's one way to do it:

WITH cteVID AS (SELECT ID, VEHICLE_TYPE, VEHICLE, MOD(ROWNUM, 2) AS VID
                  FROM VEHICLES),
     cteCars AS (SELECT ID, VEHICLE, VID
                   FROM cteVID
                   WHERE VEHICLE_TYPE = 'car'),
     cteBikes AS (SELECT ID, VEHICLE, VID
                   FROM cteVID
                   WHERE VEHICLE_TYPE = 'bike')
SELECT c.ID, c.VEHICLE AS CAR, b.VEHICLE AS BIKE
  FROM cteCars c
  INNER JOIN cteBikes b
    ON b.ID = c.ID AND
       b.VID = c.VID

It'll probably break badly if you change the underlying data, but it gives you a specific case you can work on generalizing so it will work with more complex data.

db<>fiddle here