Transposing from columns to rows

View previous topic View next topic Go down

Transposing from columns to rows

Post  nxlefrancois on Wed Sep 09, 2009 2:25 pm

I have data extracts from transaction system containing multiple measures (columns) all being of the same type of indicator, for which I would like to transform into multiple rows in my fact table.
E.g. Extract contains (for each row):

date
location
nbr_visitors_can_bc
nbr_visitors_can_on
nbr_visitors_can_qc ... (one for each of the 10 Canadian provinces)
nbr_visitors_us_ma
nbr_visitors_us_wi
nbr_visitors_us_ok ... (one for each of the 50 US states)

I'd like my FACT_VISITATION table to have 60 rows, i.e. one row for each state/province.

I have an ETL tool that would allow me to do 60 passes to load all the rows in my fact table but is there a trick to transpose, i.e. doing that all in one phase. I use Pervasive as ETL tool.
Thanks

nxlefrancois

Posts : 4
Join date : 2009-09-09

View user profile

Back to top Go down

Re: Transposing from columns to rows

Post  ngalemmo on Wed Sep 09, 2009 3:46 pm

I am not familiar with Pervasive, but most ETL tools have some means of normalizing data. If you are using Oracle 11g, it has an UNPIVOT clause in SELECT that would do it as well... other database may offer this functionality as well.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

RE: Transposing from columns to rows

Post  nxlefrancois on Thu Sep 10, 2009 9:45 am

Thanks, I will have to see if Pervasive can actually do that.

nxlefrancois

Posts : 4
Join date : 2009-09-09

View user profile

Back to top Go down

Re: Transposing from columns to rows

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum