Quick Transposing of data from multiple rows to single row fields for a given Key field.

[Former Member]

Quick Transposing of data from multiple rows to single row fields for a given Key field.

I recently encountered a requirement to transpose data from multiple rows to a single row for a given Key field.
Would like to clarify that there might be multiple ways to fashion this,however below is my take on it.

Scenario : Data in the source

Source (ZFAMILY)
Key	Members
X Family	4
Y Family	3

Source (ZFAMILY_DET)
Key	Siblings
X Family	James
X Family	Harry
X Family	Mary
X Family	Louise
Y Family	Carol
Y Family	Theresa
Y Family	Anita

Target : To achieve the below.

Target Field Structure (SAP BW)
Key	Sibling1	Sibling2	Sibling3	Sibling4
X Family	James	Harry	Mary	Louise
Y Family	Carol	Theresa	Anita

 

Sample Code to achieve this :

 

* ZFAMILY : Source Table
* ZFAMILY_DET : Source table with details on siblings.

DATA:

LT_DATA TYPE STANDARD TABLE OF ZFAMILY,
LIT_ZFAMILY_DET TYPE STANDARD TABLE OF ZFAMILY_DET,
WA_ZFAMILY_DET TYPE ZFAMILY_DET.
SELECT KEY FROM ZFAMILY INTO LT_DATA.
SELECT KEY FROM ZFAMILY_DET INTO LIT_FAMILY.

Logic :

LOOP AT LT_DATA  ASSIGNING <FS_DATA>.
LOOP AT LIT_ZFAMILY_DET INTO WA_ZFAMILY_DET WHERE KEY = <FS_DATA>-KEY
IF WA_ZFAMILY_DET-KEY IS NOT INITIAL.
IF <FS_DATA>-SIBLING1 = ''.
<FS_DATA>-SIBLING1= WA_ZFAMILY_DET-SIBLINGS.
ELSEIF <FS_DATA>-SIBLING2= ''.
<FS_DATA>-SIBLING2 = WA_ZFAMILY_DET-SIBLINGS.
ELSEIF <FS_DATA>-SIBLING3 = ''.
<FS_DATA>-SIBLING3= WA_ZFAMILY_DET-SIBLINGS.
ELSEIF <FS_DATA>-SIBLING4 = ''.
<FS_DATA>-SIBLING4 = WA_ZFAMILY_DET-SIBLINGS.
ENDLOOP.
ENDLOOP.

Cheers!
Russel