Transform the source data (1 dataset to 12 datasets)

[Rico]

Hi out there,

we have a source table which looks like:

Code Select Expand


store | year | month1 | month2 | month3 | ...
-----------------------------------------------------------
a     | 2005 | 34,56  | 32,23  | 43,30  | ....
...


To be able to dynamically select the month values it should be transformed in the framework manager to something like:

Code Select Expand


store | year | month | value
---------------------------------------------
a     | 2005 |     1 | 34,56
a     | 2005 |     2 | 32,23
a     | 2005 |     3 | 43,30
...


I know how to do it the other way around but have no idea how to make several rows out of one as in this example. Any ideas?

Best regards,
- Rico

[MFGF]

Hi,

Framework Manager is not designed to Transform data so anything of this nature would probably be done in a stored procedure, a view or a sql statement in a data source query subject.

Perhaps you could code a data source query subject using a UNION:
e.g.:

select Store, "Year", '1' as "Month", month1 as "Value" from Table

UNION ALL

select Store, "Year", '2' as "Month", month2 as "Value" from Table

UNION ALL

select Store, "Year", '3' as "Month", month3 as "Value" from Table

etc etc

Regards,

MF.

[Rico]

Thanks very much. That works exactly as I wanted it to be. Blame me for not having that idea myself  ;D

Best regards,
- Rico