SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

convert single column to multiple columns

AmivaFeb 7 2019 — edited Feb 7 2019

Hi,

Please help with the following. to get expected out put in the target table . CONVERT COLUMN product into multiple columns. with item_number combination, Products starts with 0 goes to Product 0 column,product start with 3 goes to product 3 and product start with 5 goes to product 5.

ACTUAL TABLE

CREATE TABLE XX_ITEM_PRODUCT

(ITEM_NUMBER VARCHAR2(10),PRODUCT VARCHAR2(10));

INSERT INTO XX_ITEM_PRODCUT ('1',	'0100');
INSERT INTO XX_ITEM_PRODCUT ('2',	'0101');
INSERT INTO XX_ITEM_PRODCUT ('3',	'0102');
INSERT INTO XX_ITEM_PRODCUT ('4',	'0103');
INSERT INTO XX_ITEM_PRODCUT ('1',	'3000');
INSERT INTO XX_ITEM_PRODCUT ('2',	'3001');
INSERT INTO XX_ITEM_PRODCUT ('4',	'3002');
INSERT INTO XX_ITEM_PRODCUT ('1',	'5000');
INSERT INTO XX_ITEM_PRODCUT ('4',	'5001');
INSERT INTO XX_ITEM_PRODCUT ('3',	'5002');

COMMIT;

Expected output.

PRoducts should go to the respective item number.

Target table

CREATE TABLE XX_ITEM_PRODUCT_OUTPUT

(ITEM_NUMBER VARCHAR2(10),

PRODUCT_0 VARCHAR2(10),

PRODUCT_3 VARCHAR2(10),PRODUCT_5 VARCHAR2(10));

item_number	product_0	product_3	product_5
1	0100	3000	5000
2	0101	3001
3	0102		5002
4	0103	3002	5001

This post has been answered by Frank Kulash on Feb 7 2019

Jump to Answer

Added on Feb 7 2019

2 comments

2,432 views