CDS view from rpsco table - WLP fields in column

[Guillaume_P_FR]

Hi experts.

I'm trying to build a CDS view mainly based on data from RPSCO table.

(this view is supposed to be used to feed an SAP Analytics Cloud model)

This table contains fields WLP01, WLP02 ... until WLP16, and each of them contains an amount related to january (for WLP01), to february (for WLP02) and so on until december -> WLP12 (I don't need WLP13,14,15,16).

My need is to make the following transformations : 

as to say one line from RPSCO table (with no empty wlp fields from 1 to 12) should become 12 lines.

This is kind of a "transpose" copy-paste we could make in a spreadsheet.

I need one colum for the time dimension, and one for the figures (instead of 12 columns)

My SQL is too old to achieve something like this without an outside help (and I'm new to CDS view building).

My current piece of code is looking like this : 

(I also need fields from other tables but it's another topic).

Thanks in advance to share the trick if you've solved this kind of situation already.

Regards.

Guillaume P.

[MKreitlein]

Hello @Guillaume_P_FR 

Unfortunately I am no ABAP/SQL Developer... but if you don't have one to ask, the only possibility I see here is that you create 12 views to select always one key figure.

In the end you create one final view with UNION to ingetrate all of them:

https://help.sap.com/doc/abapdocu_750_index_htm/7.50/de-de/abapunion_clause.htm

But I think the better option is to find someone who can code this 😉

Btw. for the other way round, this would be a potential solution:

https://blogs.sap.com/2019/07/16/transpose-or-merge-multiple-rows-in-one-row-cds-views/ 

BR, Martin 

[Guillaume_P_FR]

Thank you very much Martin. Very useful feedback, especially the trick of multiple views (in case we can't code something more clever). I'll keep the topic open for a while in case other good ideas pops out, if it's not the case I'll accept your answer as a solution (a colleague of mine is also investigating a "one view" solution). Thanks again.