Application Development Blog Posts
Learn and share on deeper, cross technology development topics such as integration and connectivity, automation, cloud extensibility, developing at scale, and security.
Showing results for 
Search instead for 
Did you mean: 
0 Kudos


If you are not clear about the concept of pivot/unpivot, please read this blog How to pivot/unpivot in SAP HANA by guenal

In that blog, use the HANA function MAP and SERIES_ GENERATE_ INTEGER to generate the unpivot table.

But those functions cannot be directly used in ABAP SQL, this blog will introduce a simple way to do it.


STEP1.create table "ZTSERIES"

only need two field,"mandt" and "num"

STEP2.fill table "ZTSERIES"

Add 1 to 100 to the "ZTSERIES" table, you can use se16n or sql statement.

STEP3.Prepare raw data

In the system table "FAGLFLEXT", there are 16 columns from HSL01 to HSL16


STEP4.write sql statement

you can create a report or a function module and  copy the sql statement.
SELECT a~ryear,a~racct,a~rbukrs,a~drcrk,a~activ,a~awtyp,
CONCAT( 'HSL',LPAD( CAST( b~num AS CHAR ),2,'0' ) ) AS col_name,
CASE b~num
WHEN 1 THEN hsl01
WHEN 2 THEN hsl02
WHEN 3 THEN hsl03
WHEN 4 THEN hsl04
WHEN 5 THEN hsl05
WHEN 6 THEN hsl06
WHEN 7 THEN hsl07
WHEN 8 THEN hsl08
WHEN 9 THEN hsl09
WHEN 10 THEN hsl10
WHEN 11 THEN hsl11
WHEN 12 THEN hsl12
WHEN 13 THEN hsl13
WHEN 14 THEN hsl14
WHEN 15 THEN hsl15
WHEN 16 THEN hsl16
END AS col_value
FROM v_faglflext_view AS a JOIN ztseries AS b ON b~num <= 16
ORDER BY a~ryear,a~racct,a~rbukrs,a~drcrk,a~activ,b~num
cl_demo_output=>display( lt_data ).

After running, you will see this result.



Add " JOIN ztseries AS b ON b~num <= Number of columns that need to be unpivoted " SQL statement after the table you need to be unpivoted.

You can use this method in any scenario, such as ABAP SQL, DB02, VIEW, CDS VIEW, and so on.

Thanks for reading!

Labels in this area