topic Re: Rows to Column Conversion in ABAP in Application Development and Automation Discussions

Rows to Column Conversion in ABAP

Former Member — Sat, 01 Jun 2013 15:25:19 GMT

I wanted to create a report in which I need to convert rows in to column.

e.g i have table it_zdemo contains the following values

Part	WorkWeek	Qty
A100	1301	100
B100	1302	200
C100	1304	300
A100	1305	200
A100	1306	100

I need to print the report as follows

Part	WW1301	WW1302	WW1014	WW1305	WW1306
A100	100			200	100
B100		200
C100			300

Any suggestions?

Regards

Ginee

Re: Rows to Column Conversion in ABAP

Former Member — Sat, 01 Jun 2013 16:54:08 GMT

Hi Ginee,

For my solution to be feasible, i assume that all the possible values of column 2 i.e. Work Week should be finite and known before we execute the program. If yes, then do as follows,

1) Sort the internal table by part workweek.

2) Loop at the internal table and compare the value of Workweek and on the basis of work week populate the quantity value in the field.

Code example.

Suppose you have 5 work weeks,

then

lt_data is

Part	WorkWeek	Qty
A100	1301	100
B100	1302	200
C100	1304	300
A100	1305	200
A100	1306	100

*-- Final Data Table

data : begin of ty_final,

part type .....

ww1301 type <quantity type>,

ww1302 type <quantity type>,

ww1304 type <quantity type>,

ww1305 type <quantity type>,

ww1306 type <quantity type>,

end of ty_final.

sort lt_data by part workweek.

loop at lt_Data into ls_Data.

if ls_data-workweek = 1301.

ls_final-ww1301 = ls_Data-qty.

elseif ls_data-workweek = 1302.

ls_final-ww1302 = ls_Data-qty.

elseif ......

at end of part.

append ls_final to lt_final.

clear : ls_final.

endat.

endloop.

Revert if something is not clear.

BR,

Ankit

Re: Rows to Column Conversion in ABAP

rosenberg_eitan — Sat, 01 Jun 2013 16:59:40 GMT

How many columns do you think you need ?

The reason I am asking is that is much easier to prepare a structure with known number of entries.

Regards.

Re: Rows to Column Conversion in ABAP

Former Member — Sat, 01 Jun 2013 17:36:07 GMT

How to making the column name for work week dynamic?

work week based on the date we select on the calendar

Re: Rows to Column Conversion in ABAP

Former Member — Sat, 01 Jun 2013 17:37:13 GMT

Hi ,

Total Columns for work week is 52 (1 year) , column name for work week cannot be determined, the column name for the work week

based on date selected, example :

Date Selection : -

Date : 31.05.2013

Work Week for 31.05.2013 is 22 -2013.

So the Work Week Start from 22 -2013 -----> 22-2014 ( 1 year)

Part	WorkWeek	Qty
A100	22-2013	100
B100	23-2013	200
C100	24-2013	300
A100	22-2014	200

Part

22-2013

23-2013

24-2013

25-2013

26-2013

UNTIL

22-2014

A100

100

200

B100

200

C100

300

Re: Rows to Column Conversion in ABAP

Former Member — Sat, 01 Jun 2013 17:41:21 GMT

Hi Ginee,

Then you need to go for dynamic internal tables since column names are not fixed.

Please search for dynamic internal table.

-> Please ignore this.

BR,

Ankit.

Re: Rows to Column Conversion in ABAP

rosenberg_eitan — Sat, 01 Jun 2013 17:53:50 GMT

I suggest you create a structure with 52 fields.

(I am at home so the code is not compiled or tested)

Name the fields like:

qty_01

qty_02

qty_52.

When you read it_zdemo

you can generate a name using:

concatenate 'qty_' WorkWeek+2(2) into fieldname.

and then use assign to put the value in the correct qty_ field.

regards.

Re: Rows to Column Conversion in ABAP

rosenberg_eitan — Sun, 02 Jun 2013 05:29:40 GMT

Hi,

So we are dealing with floating year.

You need to create an internal table that will be used as column pointer.

This sample deals with month but the principle is the same.

For example in one of my program I have:

TYPES: BEGIN OF tp_spmon_1 .
TYPES: spmon       TYPE spmon ,
       col_pos     TYPE fieldindex ,
       tech        TYPE lvc_s_fcat-tech .
TYPES: END OF tp_spmon_1 .

DATA: it_spmon_1 TYPE TABLE OF tp_spmon_1 .

This will be also be used when generating the field catalog for the ALV display.

I am using cl_gui_alv_grid .

regards.

Re: Rows to Column Conversion in ABAP

Noorie — Sun, 02 Jun 2013 07:52:14 GMT

Hi Ginee,

for the above case Dynamic Internal Table concept is best as we are not sure how many

entries we will have in an internal table it_zdemo.

Once u get the data in an internal table build a fieldcat.

loop at it_zdemo.

concatenate 'workweek_' it_zdemo-work_week into v_field

add_fieldcat: i_field 'Work week' ................

endloop.

now in fieldcat u will have table as u want.

* Create dynamic internal table and assign to FS

CALL METHOD cl_alv_table_create=>create_dynamic_table
    EXPORTING
      it_fieldcatalog           = it_fieldcat
    IMPORTING
      ep_table                  = it_outtab
    EXCEPTIONS
      generate_subpool_dir_full = 1
      OTHERS                    = 2.

ASSIGN it_outtab->* TO <it_outtab>.
REFRESH <it_outtab>.

* Create dynamic work area and assign to FS
CREATE DATA wa_outtab LIKE LINE OF <it_outtab>.
ASSIGN wa_outtab->* TO <wa_outtab>.

*now fill the output table.

* as your no. of rows in it_zdemo should be coloums and coloums should be rows.

Re: Rows to Column Conversion in ABAP

Former Member — Sun, 02 Jun 2013 12:03:11 GMT

data : wa_zdemo like line of it_zdemo,
       wa_zdemo1 like line of it_zdemo.

data : gv_pos type i.
data : fname type string.

* Dynamic Table Declarations

data : gt_dyn_table type ref to data,
         gw_line       type ref to data,
         gw_dyn_fcat         type lvc_s_fcat,
         gt_dyn_fcat         type lvc_t_fcat.

* Field Symbols Declarations

field-symbols: <gfs_line>,<gfs_line1>,
                <gfs_dyn_table> type standard table,
                <fs1>.

* This would create structure part WW1301 WW1302 ....

gv_pos = gv_pos + 1.
gw_dyn_fcat-fieldname = 'PART'.
gw_dyn_fcat-outputlen = 5.
gw_dyn_fcat-tabname   = 'IT_DEMO'.
gw_dyn_fcat-COLTEXT   = 'PART'.
gw_dyn_fcat-col_pos   = gv_pos.
gw_dyn_fcat-key = 'X'.
gw_dyn_fcat-key_sel = 'X'.
append gw_dyn_fcat to gt_dyn_fcat.

loop at it_zdemo into wa_zdemo.
     gv_pos = gv_pos + 1.
     concatenate 'WW' wa_zdemo-wweek into fname.
     gw_dyn_fcat-fieldname = wa_zdemo-wweek.
     gw_dyn_fcat-tabname   = 'IT_DEMO'.
     gw_dyn_fcat-COLTEXT   = fname.
     gw_dyn_fcat-outputlen = 10.
     gw_dyn_fcat-col_pos   = gv_pos.
     append gw_dyn_fcat to gt_dyn_fcat.
endloop.

* Create a dynamic internal table with this structure.

call method cl_alv_table_create=>create_dynamic_table
     exporting
       i_style_table             = 'X'
       it_fieldcatalog           = gt_dyn_fcat
     importing
       ep_table                  = gt_dyn_table
     exceptions
       generate_subpool_dir_full = 1
       others                    = 2.

    if sy-subrc eq 0.
* Assign the new table to field symbol
     assign gt_dyn_table->* to <gfs_dyn_table>.
* Create dynamic work area for the dynamic table
     create data gw_line like line of <gfs_dyn_table>.
     assign gw_line->* to <gfs_line>.
      assign gw_line->* to <gfs_line1>.
   endif.

loop at it_zdemo into wa_zdemo.
* Avoid duplicate entries for key field PART.
     READ TABLE <gfs_dyn_table> INTO <gfs_line1> WITH KEY ('PART') = wa_zdemo-part.
     if sy-subrc = 0.
          CONTINUE.
     endif.
     assign component 'PART' of structure <gfs_line> to <fs1>.
     <fs1> = wa_zdemo-part.
     unassign <fs1>.
     loop at gt_dyn_fcat into gw_dyn_fcat.
         if gw_dyn_fcat-fieldname = 'PART'.
           continue.
         endif.
         read table it_zdemo WITH key part = wa_zdemo-part wweek = gw_dyn_fcat-fieldname INTO wa_zdemo1.
         if sy-subrc = 0.
            assign component gw_dyn_fcat-fieldname of STRUCTURE <gfs_line> to <fs1>.
            <fs1> = wa_zdemo1-qty.
            unassign <fs1>.
         endif.
     endloop.
     append <gfs_line> to <gfs_dyn_table>.
     clear: <gfs_line>.
endloop.

write :/.
      loop at gt_dyn_fcat into gw_dyn_fcat.
           write (10) : gw_dyn_fcat-coltext.
       endloop.
write :/.
loop at <gfs_dyn_table> into <gfs_line>.
      loop at gt_dyn_fcat into gw_dyn_fcat.
           assign component gw_dyn_fcat-fieldname of STRUCTURE <gfs_line> to <fs1>.
           write : <fs1>.
       endloop.
      write 😕      .
endloop.

Re: Rows to Column Conversion in ABAP

Former Member — Sun, 02 Jun 2013 15:25:01 GMT

Hi Eitan ,

Thanks for your advice.

Re: Rows to Column Conversion in ABAP

Former Member — Sun, 02 Jun 2013 15:25:48 GMT

Hi Thanks for your advice.

Re: Rows to Column Conversion in ABAP

Former Member — Sun, 02 Jun 2013 15:26:11 GMT

Hi Thanks for your advice.

Re: Rows to Column Conversion in ABAP

Former Member — Sun, 02 Jun 2013 15:26:34 GMT

Hi Thanks for your advice.

Re: Rows to Column Conversion in ABAP

Former Member — Sun, 02 Jun 2013 15:29:35 GMT

Hi Susmitha ,

Issue resolved. Many thanks for the code you provided!

Re: Rows to Column Conversion in ABAP

rosenberg_eitan — Mon, 03 Jun 2013 05:35:55 GMT

Hi,

You are most welcome.

But not to leave loose ends I decided to do some coding to show alternative solution
using fix number of columns.

This is a fresh program so please check.....

You might need to run SAPBC_DATA_GENERATOR to generate some data on your machine.

Re: Rows to Column Conversion in ABAP

Former Member — Tue, 04 Jun 2013 07:48:54 GMT

Hi Eitan ,

Many thanks for the code you provided!

Regerds

Ginee

Re: Rows to Column Conversion in ABAP

rosenberg_eitan — Tue, 04 Jun 2013 08:32:35 GMT

Hi,

Again you are most welcome.

Regerds.

If you try it please give me a feed back.

Thanks.

Re: Rows to Column Conversion in ABAP

Former Member — Wed, 31 Jul 2013 14:23:50 GMT

This message was moderated.