Transform Row Data to Columns

[Former Member]

Anyone have an efficient/elegant way to transform row data into columns in a BL transaction?

Start with this:

ID Date Property Result

1 01-Jan-07 First 1

1 01-Jan-07 Second 2

2 01-Jan-07 First 3

2 01-Jan-07 Second 4

Create this

ID Date First Second

1 01-Jan-07 1 2

2 01-Jan-07 3 4

What I do right now is

1) query for data

2) determine distinct properties

2) repeat on distinct properties

3) filter query data for distinct property

4) full outer join between current property and previous properties

5) Output xml after repeater completes

This works, but it is very slow.

[Former Member · Accepted solution]

Andrew,

Try the InterpolatedValues inline transform on the Query Template. It might get you closer to where you want to be.

[Former Member · Accepted solution]

Thanks Ryan,

I took a look at the transform, and yes it will do the same type of thing. Unfortunately, it is even slower than the BL method.

My BL transaction takes ~25 seconds to transform 1000 rows of data and return an HTML output, the inline transform takes ~55 seconds to do the same.

[Former Member · Accepted solution]

Wow, those are interesting results.

In your step #3 on your first post, how are you filtering? Are you taking advantage of XPATH?

[Former Member · Accepted solution]

Just had my first exposure to XPATH efficiency via the WIKI yesterday...looks like I've got to do some studying.

Is there an XPATH for Dummies...or better yet an XPATH for Business User dummies <grin>.

As for my filter method, I use the Sort/Filter action block to filter my complete dataset based on the property the distinct value repeater is currently on.

The Joiner branch of my BL transaction contains 5 action blocks:

Filter/Sort

Column Stripper

Column Alias

Joiner

Assignment

Do you think I can make some gains by comressing this branch via XPATH?

[Former Member · Accepted solution]

Andrew,

A good place to start learning about XPath is w3schools.com, they have free online tutorials. Goto this site:

http://www.w3schools.com/xpath/default.asp

As far as I'm aware there is no XPath for Dummies, but you may want to try XSLT for Dummies, it touches on XPath, although I would start with w3schools first.

[jcgood25]

Andrew,

What is the underlying source of you data query? If this is a relational database query you might just simply consider creating a stored procedure and just passing it the necessary parameters to return the resultset in your desired format of:

ID Date First Second

1 01-Jan-07 1 2

2 01-Jan-07 3 4

Regards,

Jeremy Good

[Former Member]

Jeremy, a stored procedure would probably increase the execution speed considerably, but it comes with a change management cost.

I likely won't pursue this option unless I can't make any progress on the xMII side.

Thanks for your response.