I want to transpose row into column by category.. below is my data and my expectation

sorry for bad english and question.. I dont know how to phase it as english is not my primary language..

here is my data and my expectation

Insert SQL:

CREATE TABLE OBJECT(
    ID NUMBER,
    TYPE VARCHAR2(10),
    NAME VARCHAR2(10)
);

INSERT INTO OBJECT (ID, TYPE, NAME ) VALUES 
    (1,'FISH','Shark'),
    (2,'FISH','Carp'),
    (3,'FISH','Salmon'),
    (4,'ANIMAL','Cat'),
    (5,'ANIMAL','Dog'),
    (6,'ANIMAL','Sheep'),
    (7,'ANIMAL','Lion'),
    (8,'TRANS','Car'),
    (9,'TRANS','Bike'),
    (10,'FRUIT','Mango'),
    (11,'FRUIT','Apple'),
    (12,'FRUIT','Orange'),
    (13,'FRUIT','Banana'),
    (14,'FRUIT','Grape')
;

CodePudding user response：

Number the rows for each type and then PIVOT around the row number and type:

SELECT fish_id,
       fish_name,
       animal_id,
       animal_name,
       trans_id,
       trans_name,
       fruit_id,
       fruit_name
FROM   (
  SELECT o.*,
         ROW_NUMBER() OVER (PARTITION BY type ORDER BY id) AS rn
  FROM   object o
)
PIVOT (
  MAX(id) AS id,
  MAX(name) AS name
  FOR type IN (
    'FISH'   AS fish,
    'ANIMAL' AS animal,
    'TRANS'  AS trans,
    'FRUIT'  AS fruit
  )
)

Which, for the sample data:

CREATE TABLE OBJECT(
    ID NUMBER,
    TYPE VARCHAR2(10),
    NAME VARCHAR2(10)
);

INSERT ALL 
  INTO OBJECT (ID, TYPE, NAME ) VALUES (1,'FISH','Shark')
  INTO OBJECT (ID, TYPE, NAME ) VALUES (2,'FISH','Carp')
  INTO OBJECT (ID, TYPE, NAME ) VALUES (3,'FISH','Salmon')
  INTO OBJECT (ID, TYPE, NAME ) VALUES (4,'ANIMAL','Cat')
  INTO OBJECT (ID, TYPE, NAME ) VALUES (5,'ANIMAL','Dog')
  INTO OBJECT (ID, TYPE, NAME ) VALUES (6,'ANIMAL','Sheep')
  INTO OBJECT (ID, TYPE, NAME ) VALUES (7,'ANIMAL','Lion')
  INTO OBJECT (ID, TYPE, NAME ) VALUES (8,'TRANS','Car')
  INTO OBJECT (ID, TYPE, NAME ) VALUES (9,'TRANS','Bike')
  INTO OBJECT (ID, TYPE, NAME ) VALUES (10,'FRUIT','Mango')
  INTO OBJECT (ID, TYPE, NAME ) VALUES (11,'FRUIT','Apple')
  INTO OBJECT (ID, TYPE, NAME ) VALUES (12,'FRUIT','Orange')
  INTO OBJECT (ID, TYPE, NAME ) VALUES (13,'FRUIT','Banana')
  INTO OBJECT (ID, TYPE, NAME ) VALUES (14,'FRUIT','Grape')
SELECT * FROM DUAL;

Outputs:

fiddle