-------------------------------------------------------------------------------

-- Part 2: Pivot the table, Join the file to itself and distribute the

-- BU's depending on their values.

-- The result will be a "staircase" shape, where each material

-- number has a separate BU entry.

-------------------------------------------------------------------------------

SELECT DISTINCT

V01.MATERIAL_NUMBER MATERIAL_NUMBER ,

V01.NOTES NOTES ,

'EN' LANGUAGE ,

CASE WHEN STRIP(V01.IBMCU) = '00001' THEN '00001' ELSE ' ' END BU00001,

CASE WHEN STRIP(V02.IBMCU) = '00002' THEN '00002' ELSE ' ' END BU00002,

CASE WHEN STRIP(V03.IBMCU) = '00003' THEN '00003' ELSE ' ' END BU00003,

CASE WHEN STRIP(V04.IBMCU) = '00004' THEN '00004' ELSE ' ' END BU00004,

CASE WHEN STRIP(V05.IBMCU) = '00005' THEN '00005' ELSE ' ' END BU00005,

CASE WHEN STRIP(V06.IBMCU) = '00006' THEN '00006' ELSE ' ' END BU00006,

CASE WHEN STRIP(V07.IBMCU) = '00007' THEN '00007' ELSE ' ' END BU00007,

CASE WHEN STRIP(V08.IBMCU) = '00008' THEN '00008' ELSE ' ' END BU00008,

CASE WHEN STRIP(V09.IBMCU) = '00009' THEN '00009' ELSE ' ' END BU00009,

CASE WHEN STRIP(V10.IBMCU) = '00010' THEN '00010' ELSE ' ' END BU00010,

CASE WHEN STRIP(V11.IBMCU) = '00011' THEN '00011' ELSE ' ' END BU00011,

CASE WHEN STRIP(V12.IBMCU) = '00012' THEN '00012' ELSE ' ' END BU00012,

CASE WHEN STRIP(V13.IBMCU) = '00013' THEN '00013' ELSE ' ' END BU00013,

CASE WHEN STRIP(V14.IBMCU) = '00014' THEN '00014' ELSE ' ' END BU00014,

CASE WHEN STRIP(V15.IBMCU) = '00015' THEN '00015' ELSE ' ' END BU00015,

CASE WHEN STRIP(V16.IBMCU) = '00016' THEN '00016' ELSE ' ' END BU00016

FROM EACPMM32DX V01

LEFT OUTER JOIN EACPMM32DX V02

on V01.IBMCU = V02.IBMCU

and V01.MATERIAL_NUMBER = V02.MATERIAL_NUMBER

AND STRIP(V02.IBMCU ) = '00002'

LEFT OUTER JOIN EACPMM32DX V03

on V01.IBMCU = V03.IBMCU

and V01.MATERIAL_NUMBER = V03.MATERIAL_NUMBER

AND strip(V03.IBMCU ) = '00003'

LEFT OUTER JOIN EACPMM32DX V04

on V01.IBMCU = V04.IBMCU

and V01.MATERIAL_NUMBER = V04.MATERIAL_NUMBER

AND strip(V04.IBMCU ) = '00004'

LEFT OUTER JOIN EACPMM32DX V05

on V01.IBMCU = V05.IBMCU

and V01.MATERIAL_NUMBER = V05.MATERIAL_NUMBER

AND strip(V05.IBMCU ) = '00005'

LEFT OUTER JOIN EACPMM32DX V06

on V01.IBMCU = V06.IBMCU

and V01.MATERIAL_NUMBER = V06.MATERIAL_NUMBER

AND strip(V06.IBMCU ) = '00006'

LEFT OUTER JOIN EACPMM32DX V07

on V01.IBMCU = V07.IBMCU

and V01.MATERIAL_NUMBER = V07.MATERIAL_NUMBER

AND strip(V07.IBMCU ) = '00007'

LEFT OUTER JOIN EACPMM32DX V08

on V01.IBMCU = V08.IBMCU

and V01.MATERIAL_NUMBER = V08.MATERIAL_NUMBER

AND strip(V08.IBMCU ) = '00008'

LEFT OUTER JOIN EACPMM32DX V09

on V01.IBMCU = V09.IBMCU

and V01.MATERIAL_NUMBER = V09.MATERIAL_NUMBER

AND strip(V09.IBMCU ) = '00009'

LEFT OUTER JOIN EACPMM32DX V10

on V01.IBMCU = V10.IBMCU

and V01.MATERIAL_NUMBER = V10.MATERIAL_NUMBER

AND strip(V10.IBMCU ) = '00010'

LEFT OUTER JOIN EACPMM32DX V11

on V01.IBMCU = V11.IBMCU

and V01.MATERIAL_NUMBER = V11.MATERIAL_NUMBER

AND strip(V11.IBMCU ) = '00011'

LEFT OUTER JOIN EACPMM32DX V12

on V01.IBMCU = V12.IBMCU

and V01. MATERIAL_NUMBER = V12.MATERIAL_NUMBER

AND strip(V12.IBMCU ) = '00012'

LEFT OUTER JOIN EACPMM32DX V13

on V01.IBMCU = V13.IBMCU

and V01.MATERIAL_NUMBER = V13.MATERIAL_NUMBER

AND strip(V13.IBMCU ) = '00013'

LEFT OUTER JOIN EACPMM32DX V14

on V01.IBMCU = V14.IBMCU

and V01.MATERIAL_NUMBER = V14.MATERIAL_NUMBER

AND strip(V14.IBMCU ) = '00014'

LEFT OUTER JOIN EACPMM32DX V15

on V01.IBMCU = V15.IBMCU

and V01.MATERIAL_NUMBER = V15.MATERIAL_NUMBER

AND strip(V15.IBMCU ) = '00015'

LEFT OUTER JOIN EACPMM32DX V16

on V01.IBMCU = V16.IBMCU

and V01.MATERIAL_NUMBER = V16.MATERIAL_NUMBER

AND strip(V16.IBMCU ) = '00016'