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.
cancel
Showing results for 
Search instead for 
Did you mean: 
xiaosanyu
Participant
0 Kudos

Introduction


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
INTO TABLE @DATA(lt_data)
UP TO 160 ROWS.
cl_demo_output=>display( lt_data ).

After running, you will see this result.


 

Summary


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!

 
4 Comments
Labels in this area