I have a tilde-delimited UD field on a quote/order detail line (with from zero to nine values) that I can break out into calculated fields. But the fields all come out on one row.
These values come from a configurator, and basically describe the major part numbers making up the configured part.
I’d like to create some type of subquery that would return from zero to nine rows of data that looks like the original order detail row, with one column having the sub-model parts instead of one row with nine columns of calculated fields.
I assume I could make nine Union subqueries, but am afraid of the time it would take to run them.
The Pivot subquery does the opposite of what I want to do. It takes rows with unique values in a column, pivots the rows and the unique values become column headers. You can then aggregate data falling under the new column headers.
What I’m wanting to do takes values in one row of data and transforms one row into multiple rows with the columnar data in one column of each row.
I ran across an “Unpivot” feature in SQL that is supposed to do something like that. But, of course, it’s not available in the BAQ designer.
I can get the same result with Union subqueries. I need one subquery per column. This tilde-delimited field may contain up to nine fields, so I need nine union subqueries, plus the top level subquery.
The top level subquery may be complex. It’s connected with sales history data, which can be used in a variety of ways. With the Union subqueries, it looks like I would need to duplicate the whole query and change only the particular portion of the tilde-delimited field in each. I would need to match the data types on each column and the repeating data like invoice number, order number, customer number, etc.
As an alternative, I’m wondering about External BAQs. Can you link an external BAQ to a regular BAQ or visa versa? My thinking is in one BAQ I would use a set of Union subqueries to return just the basic info for this set of data. Then I would link to the other BAQ to pull in the data like a regular table.