Hi,
My question is regarding transpose of rows into columns:
BANNER_CODE/DIV_CODE/LEG_MATNR/SAP_MATNR/MAKTX/LEG_MATKL/SAP_MATKL/LEG_WHERL/SAP_WHERL/
CS/1/10137/58351/BAKE KING CHOCOLATE RICE 160G/384/10203004/34/SG/1
GH/1/36762/212615/MYOJO BOWL NDLE THAI TOM YAM 105G/499/10103001/34/SG/2
CS/1/36762/212615/MYOJO BOWL NDLE THAI TOM YAM 105G/499/10103001/34/SG/3
SE/1/212615/212615/MYOJO BOWL NDLE THAI TOM YAM 105G/11060104/10103001/SG/SG/4
GH/6/56256/276019/GMS SHIN MIN 1COPY/1862/60507006/34/SG/5
CS/6/56256/276019/GMS SHIN MIN 1COPY/1862/60507006/34/SG/6
SE/6/276019/276019/GMS SHIN MIN 1COPY/15040101/60507006/SG/SG/7
GH/6/27112/110005765/KOMAX BIOKIPS FOOD CONTAINER 3.1L/2108/60207005/17/CN/8
I've sample data as above. My requirement is to transpose all those records into column based on the key column(SAP_MATNR) and banner_code.
So, the output i required is somewhat similar to below format.
SAP_MATNR/DIV_CODE_GH/DIV_CODE_CS/DIV_CODE_SE/LEG_MATNR_GH/LEG_MATNR_CS/LEG_MATNR_SE/LEG_MATKL_GH/LEG_MATKL_CS/LEG_MATKL_SE/SAP_MATKL_GH/SAP_MATKL_CS/SAP_MATKL_SE
58351//1///10137///384///10203004/
212615/1/1/1/36762/36762/212615/499/499/11060104/10103001/10103001/10103001
276019/6/6/6/56256/56256/276019/1862/1862/15040101/60507006/60507006/60507006
110005765/6///27112////2108///60207005/
In the above output, SAP_MATNR column will be used as a key, banner_code value will be suffixed with all the columns heading and respective values will be put here.
I've gone through many articles related to PIVOT queries but somehow not able to find a way to do this dynamically. The list of columns and rows are samples only as in my original requirement i've around 100+ fields whose values has to be displayed in this format. Kindly help me providing the best dynamic solution for this.
Any columns not in the pivot clause form an implicit group by. And you can use many aggregates in the first part of the pivot. So:
- Use a subquery to select all the columns you need in your output
- Aggregate all the columns you want to pivot. Make sure you alias them!
- Pivot banner_code by CS and GH.
Which gives:
create table am_art_basic (
banner_code varchar2( 100 ),
div_code varchar2( 100 ),
leg_matnr varchar2( 100 ),
sap_matnr varchar2( 100 ),
maktx varchar2( 100 ),
leg_matkl varchar2( 100 ),
sap_matkl varchar2( 100 ),
leg_wherl varchar2( 100 ),
sap_wherl varchar2( 100 )
);
Insert into AM_ART_BASIC
(BANNER_CODE, DIV_CODE, LEG_MATNR, SAP_MATNR, MAKTX,
LEG_MATKL, SAP_MATKL, LEG_WHERL, SAP_WHERL)
Values
('SE', '6', '276019', '276019', 'GMS SHIN MIN 1COPY',
'15040101', '60507006', 'SG', 'SG');
Insert into AM_ART_BASIC
(BANNER_CODE, DIV_CODE, LEG_MATNR, SAP_MATNR, MAKTX,
LEG_MATKL, SAP_MATKL, LEG_WHERL, SAP_WHERL)
Values
('SE', '1', '212615', '212615', 'MYOJO BOWL NDLE THAI TOM YAM 105G',
'11060104', '10103001', 'SG', 'SG');
Insert into AM_ART_BASIC
(BANNER_CODE, DIV_CODE, LEG_MATNR, SAP_MATNR, MAKTX,
LEG_MATKL, SAP_MATKL, LEG_WHERL, SAP_WHERL)
Values
('CS', '1', '010137', '58351', 'BAKE KING CHOCOLATE RICE 160G',
'000384', '10203004', '34', 'SG');
Insert into AM_ART_BASIC
(BANNER_CODE, DIV_CODE, LEG_MATNR, SAP_MATNR, MAKTX,
LEG_MATKL, SAP_MATKL, LEG_WHERL, SAP_WHERL)
Values
('CS', '1', '036762', '212615', 'MYOJO BOWL NDLE THAI TOM YAM 105G',
'000499', '10103001', '34', 'SG');
Insert into AM_ART_BASIC
(BANNER_CODE, DIV_CODE, LEG_MATNR, SAP_MATNR, MAKTX,
LEG_MATKL, SAP_MATKL, LEG_WHERL, SAP_WHERL)
Values
('CS', '6', '056256', '276019', 'GMS SHIN MIN 1COPY',
'001862', '60507006', '34', 'SG');
Insert into AM_ART_BASIC
(BANNER_CODE, DIV_CODE, LEG_MATNR, SAP_MATNR, MAKTX,
LEG_MATKL, SAP_MATKL, LEG_WHERL, SAP_WHERL)
Values
('GH', '6', '027112', '110005765', 'KOMAX BIOKIPS FOOD CONTAINER 3.1L',
'002108', '60207005', '17', 'CN');
Insert into AM_ART_BASIC
(BANNER_CODE, DIV_CODE, LEG_MATNR, SAP_MATNR, MAKTX,
LEG_MATKL, SAP_MATKL, LEG_WHERL, SAP_WHERL)
Values
('GH', '6', '056256', '276019', 'GMS SHIN MIN 1COPY',
'001862', '60507006', '34', 'SG');
Insert into AM_ART_BASIC
(BANNER_CODE, DIV_CODE, LEG_MATNR, SAP_MATNR, MAKTX,
LEG_MATKL, SAP_MATKL, LEG_WHERL, SAP_WHERL)
Values
('GH', '1', '036762', '212615', 'MYOJO BOWL NDLE THAI TOM YAM 105G',
'000499', '10103001', '34', 'SG');
COMMIT;
select * from (
select sap_matnr, banner_code, leg_matkl, div_code
from am_art_basic
) pivot (
min(div_code) div_code,
min(leg_matkl) matkl
for ( banner_code ) in (
'CS' cs, 'GH' gh
)
);
SAP_MATNR CS_DIV_CODE CS_MATKL GH_DIV_CODE GH_MATKL
212615 1 000499 1 000499
276019 6 001862 6 001862
58351 1 000384 <null> <null>
110005765 <null> <null> 6 002108