Quick Transposing of data from multiple rows to single row fields for a given Key field.
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