Skip to Content
Technical Articles
Author's profile photo xiaosan yu

How to unpivot in ABAP SQL

Introduction

If you are not clear about the concept of pivot/unpivot, please read this blog How to pivot/unpivot in SAP HANA by Halil 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!

 

Assigned Tags

      4 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Chad He
      Chad He

      It's a interesting SQL trick.

      Thank you for your blog.

      Author's profile photo xiaosan yu
      xiaosan yu
      Blog Post Author

      Thank you for your comment 🙂

      Author's profile photo Rodrigo Giner de la Vega
      Rodrigo Giner de la Vega

      Why create an entire transparent table just for enum the series when you could just use an internal table for the same porpuse ? Now you can use internal table in the FROM section

      Regards

      Author's profile photo xiaosan yu
      xiaosan yu
      Blog Post Author

      Yeah, you can use an internal table in the report or function module, but you need additional coding, and can not use it in View/CDS View.