Rows to Column Conversion in ABAP

[Former Member]

Hi

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

[Former Member]

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 ......

.

.

.

.

<Continue till all the work weeks>

at end of part.

append ls_final to lt_final.

clear : ls_final.

endat.

endloop.

Revert if something is not clear.

BR,

Ankit

[Former Member]

Hi

How to making the column name for work week dynamic? 

work week based on the date we  select on the calendar

[rosenberg_eitan]

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.

[Former Member]

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

[Former Member]

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.

[rosenberg_eitan]

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.

[Former Member]

Hi  Eitan ,

Thanks for your advice.

[Former Member]

Hi  Thanks for your advice.

[rosenberg_eitan]

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.

[Former Member]

Hi Eitan ,

Many thanks for the code you provided!

Regerds

Ginee

[rosenberg_eitan]

Hi,

Again you are most welcome.

Regerds.

If you try it please give me a feed back.

Thanks.

[rosenberg_eitan]

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.

[Former Member]

Hi  Thanks for your advice.

[Noorie]

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.

[Former Member]

Hi  Thanks for your advice.

[Former Member]

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.

[Former Member]

Hi  Susmitha ,

Issue resolved. Many thanks for the code you provided!

[Former Member]

This message was moderated.