Hi,
I want to use reverse pivot to change my source like that :
ID VALUE ID VALUE_1 VALUE_2
1 1 1 1 0,8
1 0,8 ------> 2 1 0,75
2 1
2 0,75
Reading the documentation i saw that i must specify some ‘Axis Value’ to realise the reverse pivot.
I don’t understand why ?
Can we make it general ?
Can some one explain me how does Reverse Pivot work ?
Gaël
gchanteu 
(BOB member since 2007-06-29)
Yes, you need an axis value. In your case there is no column specifying the column it should go to - why is value_2 = 0.8 and not value_1? Because of its position. Hence the axis value will be a counter per group, first row has axis_value=1, second axis_value=2, third - it’s another group - axis value 1,…
In 11.7 you can create that axis_value easily by using the gen_row_num_per_group(ID) function https://boc.sdn.sap.com/node/5521
Werner Daehn 
(BOB member since 2004-12-17)
Thanks for the answer.
I will use 2 query to get my value separatly and than a self join … I think it’s easier.
Thanks again
gchanteu (BOB member since 2007-06-29)
Werner Daehn 
(BOB member since 2004-12-17)
I’m also needing some help. What if the number of rows per group varies, not fixed. In the example given, it is fixed to 2 rows per group, what if group 1 has 3 rows and group 2 has 4 rows etc.
In fact what I need is say we are dealing with some groups running into 100 rows per group and want to output 1 row only with the non pivot columns and then either 1 column with all the row values for that column concatenated together or stored in separate columns.
Any ideas?
gchanteu, can you elobrate what you mean by " will use 2 query to get my value separatly and than a self join" and can it work with my scenario above where the number of rows per group varies?
JOJOY 
(BOB member since 2005-07-27)
I’m also needing some help. What if the number of rows per group varies, not fixed. In the example given, it is fixed to 2 rows per group, what if group 1 has 3 rows and group 2 has 4 rows etc.
In fact what I need is say we are dealing with some groups running into 100 rows per group and want to output 1 row only with the non pivot columns and then either 1 column with all the row values for that column concatenated together or stored in separate columns.
Any ideas?
gchanteu, can you elobrate what you mean by " will use 2 query to get my value separatly and than a self join" and can it work with my scenario above where the number of rows per group varies?
JOJOY (BOB member since 2005-07-27)
A table can have as many rows as you want, but the number of columns is fixed. So when you use a reverse pivot, you need to specify the upper limit of columns. Just imagine your target table has 100columns and suddenly your source has 200 (different) rows for a pivot set. What should happen then?
Werner Daehn (BOB member since 2004-12-17)
I know what you mean but actually … and I’m sure others also have had this need; I want to collapse the rows and transform specific input column(s) of the N rows into a concatenated string (in new output column(s)) identified by the non-pivot columns.
So the reverse pivot first comes into mind as a halfway technique to getting there … but is it?
From:
1 AAA XXX
1 BC MM
1 QW KKKK
2 GG HHHH
2 NNN SS
9 Z BVBV
9 A NBNBN
9 QQ UPUP
9 IIII FGGFGFF
To:
1 AAA|XXX--BC|MM--QW|KKKK
2 GG|HHHH--NNN|SS
9 Z|BVBV--A|NBNBN--QQ|UPUP--IIII|FGGFGFF
Producing a concise output per common id, yknow the usual applications are in like multiple address lines into 1 line, multiple surname, firstname entries into list of names, multiple city entries into list of cities etc. The key in these types of data structures is there is no pre-known finite list of axis column values - hence N rows, could be 1 could be 200 rows. How do we do this in DI the tool of choice?
JOJOY (BOB member since 2005-07-27)
Some databases do have aggregation functions like “sum(varchar)” to concatenate text. Nice idea - reverse pivot is the only choice we have I think.
Werner Daehn (BOB member since 2004-12-17)
Hi JOYJOY
This is what i have done to transforme the data below :
ID CODE VALUE ID VALUE_1 VALUE_2 VALUE_3
1 V 1 1 1 2 2
1 J 2 ------>
1 R 2
With 3 query i get the 3 rows (with the CODE in the where clause) and than in another query i join the 3 query by the ID.
With this solution i don’t need to know the value (axis value)
gchanteu 
(BOB member since 2007-06-29)
Actually, CODE is your axis column!
Werner Daehn (BOB member since 2004-12-17)
Ya I wish it were that simple honestly, as wdaehn said, in your case you KNOW the values upfront hence they are your axis values. If you had used the Reverse_Pivot transform and set CODE to be the Axis column and added 3 Axis value rows (1xV, 1xJ, 1xR) with same prefix value, I believe your output would in fact be labelled automatically by DI as V_VALUE, J_VALUE, R_VALUE.
In my case I’m asking for ability for when we can’t possible supply all the axis values into the transform. And there are so many reasons for it being so - for one eg because there are too many (try typing up 200 values), because they aren’t necessarily unique, because they could be any varchar. :?
JOJOY (BOB member since 2005-07-27)
Ok so my post was not useless because i did, really, not understand how reverse pivot work.
I change my DF and it’s work fine. Thanks a lot. 
Now JOYJOY i understand your problem but i don’t have any solution ( … as you can understand )
Gaël
gchanteu (BOB member since 2007-06-29)
You’re welcome I thought you’d be a happy camper followin my post. Lucky you but also its always great when someone has their prob solved. So never a pointless question
JOJOY (BOB member since 2005-07-27)