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:
FISH_ID | FISH_NAME | ANIMAL_ID | ANIMAL_NAME | TRANS_ID | TRANS_NAME | FRUIT_ID | FRUIT_NAME |
---|---|---|---|---|---|---|---|
1 | Shark | 4 | Cat | 8 | Car | 10 | Mango |
2 | Carp | 5 | Dog | 9 | Bike | 11 | Apple |
3 | Salmon | 6 | Sheep | null | null | 12 | Orange |
null | null | 7 | Lion | null | null | 13 | Banana |
null | null | null | null | null | null | 14 | Grape |