Reverse Pivot Dynamically?

dataservices
1

I’ve searched a few times and i’ve been unable to find a good method of dynamically setting the output columns within a reverse pivot group… for a project, we currently receive a list of field changes from an audit table in the following format:

row_id, account_num, field changed, new amount, old amount, date_changed
1, 555, ‘phone_num’, ‘555-5555’, ‘555-5556’, 3/31/2012
2, 555, ‘address2’, ‘apt 3’, ‘apt 4b’, 3/31/2012

…

i’m looking for a way to un-pivot these to allow map operations like the table below:

operation, account_num, phone num, address2, date changed
‘update’, 555, ‘555-5556’, ‘apt 4b’, 3/31/2012

(‘update’ will be static for each row)

currently, we’re using sql server '05 and BODS 14.0…

i’ve seen suggestions in the past on using a running number in each group… but i cant get the gen_row_num_by_group to work while keeping the ‘field_changed’ consistent for each change in the target.

any ideas where im going wrong? :crazy_face:

we have over 83 fields that we’re looking to update, with more to be sporadically added in the future…

ww55d (BOB member since 2011-06-14)

2

No need for a gen_row_num.

Non-pivot columns: account_num
pivot axis column: field_changed
pivoted columns: new_amount, date_changed
Axis values: “phone_num”, “address2”

(remove row_id, not needed and will screw up the sort)

This will turn
account_num, field changed, new amount, old amount, date_changed
555, ‘phone_num’, ‘555-5555’, ‘555-5556’, 3/31/2012
555, ‘address2’, ‘apt 3’, ‘apt 4b’, 3/31/2012

into

account_num, phone_num_new_amount, address2_new_amount, phone_num_date_changed, address2_date_changed
555, ‘555-5556’, ‘apt 4b’, 3/31/2012, 3/31/2012

and the rest is done in a query.

I guess we are not done yet because of e.g. only the phone_num did change, the address2_… fields will be empty.

http://wiki.sdn.sap.com/wiki/display/EIM/Reverse+Pivot

Werner Daehn :de: (BOB member since 2004-12-17)

3

Werner, thank you for your quick reply.

We are looking to have the axis values set semi-dynamically. Telephone and address2 are only 2 examples of the potentially hundreds to be tracked.

The linked article hints to this at the end as it discusses the get_row_num_by_group function. Because of the large number of axis value and the potential changes in the future, we are hoping for a not to have to manually maintain this.

ww55d (BOB member since 2011-06-14)

4

Well, that is kind of a problem. The output should be a structure of

account_num, phone num, address2, date changed

and yet flexible? So what should happen to the output and the target table when there is a new column?

Werner Daehn :de: (BOB member since 2004-12-17)

5

sorry… i mis-represented my output…

the target output ideally would be :

operation, account_num, phone num, address2, fieldx, field y, … , date changed
‘update’, 555, ‘555-5556’, ‘apt 4b’, field_x_val, field_y_val, …, 3/31/2012

ideally, this would dynamically grow width-wise with the addition of more fields to the field_changed column.

ww55d (BOB member since 2011-06-14)