CDS View Pivot or Tranpose

[Former Member]

Hi Experts,

I am trying to develop a simple Summary Report in ABAP. However, due to the very large data volume, we decided to leverage HANA by using SALV-IDA and moving all processing to the DB Level via CDS Views.

Below is a simplified version of what I'm trying to accomplish. The goal is to be able to compare fields one-by-one per Document Item and ultimately display it as Item and Category Summaries. In the example below we see that the Quantity, Amount and Currency Fields are different for Document 1 Item 1.

The complication revolves around the table ZITEMS Table. This Contains Versions (at most 2) of the same Document Item as rows. I figured to be able to do comparisons and aggregates, I need to create an intermediate view which basically transpose/pivot the version and the comparison fields(material,qty,etc).

I'm quite new to SQL Script and SQL programming in general and have no idea how this is done. I'm used to the traditional approaching of pulling everything via OpenSQL and processing in ABAP. I would like to get some pointers on how this can be done.

Thanks!

-Emir

cds-view-problem.jpg(316.9 kB)

[Former Member]

Hi Emir,

Could you please let us know your approach you used to solve this. I have a similar requirement and could use some inputs.

[Former Member]

Solved on my Own.

Solution used LEFT OUTER JOIN on the same table. I did this per field. So you need to create one query for each of the fields and combine them all together via UNION.

[RohitSingh]

Hi Emir,

Could you please let us know your approach you used to solve this. I have a similar requirement and could use some inputs.

[viren_sharma]

I have the same requirement, can you please me with your way/code how did you achieve this?

[theequalizer]

i used following approach:

define view ZI_PIVOT( keyField, selectField ) 
as select from xxx{ key abc, fieldAlpha } 
union all select from xxx{ keyField, fieldBeta } 
union all select from xxx{ keyField, fieldGamma }<br>

So you get from |Key|fieldAlpha|fieldBeta|fieldGamma to Key|field

key1|X|E|Z to

key1|X
key1|E
key1|Z

if you use UNION instead of UNION ALL - the duplicate entries will be removed automatically