I have two MYSQL tables Products and Prices. Both tables are populated via a webform that creates a pipe delimited value for the options that the end user has selected. This process and the initial output cannot be changed.
So if User1 selects 3 products, they will have a string with three products and two pipes. If User 2 selects 7 products they will have a string with 7 products and 6 pipes.
The separate prices table will then have corresponding pipe delimited values for the prices for each of the items selected by the user.
I am wanting to attribute the price to the product, but the only linking value is there location within a pipe delimited string whose length fluctuates based on the number of items selected.
Products Table
| Account ID | Options |
|---|---|
| s001 | Cat|Dog|Cow|Sheep |
| s002 | Cat|Dog|Cow|Sheep |
| s003 | Cat|Dog|Sheep |
| s004 | Cat|Dog|Sheep |
| s005 | Cat|Dog|Sheep |
| s006 | Cat|Dog|Cow|Sheep |
| s007 | Cat|Dog|Cow|Pig|Goat|Seagull|Sheep |
| s008 | Duck |
| s009 | Pig|Goat|Seagull|Sheep |
| s010 | Cat|Dog|Cow|Sheep|Seal |
Prices Table
| Account ID | optionsdiscounts |
|---|---|
| s001 | -40.00|0.00|-40.00|0.00 |
| s002 | -40.00|0.00|-40.00|0.00 |
| s003 | -40.00|0.00|0.00 |
| s004 | -5.50|0.00|0.00 |
| s005 | -40.00|0.00|0.00 |
| s006 | -40.00|0.00|0.00 |
| s007 | -15.00|0.00|-15.00|-15.00|0.00|-15.00|0.00 |
| s008 | -13.85 |
| s009 | -33.71|0.00|-33.71|0.00 |
| s010 | -40.00|0.00|-40.00|0.00 |
So using the tables above I need to say that for:
- s001, Cat is -40, and Cow is -40
- s005, Sheep (Position 3) is 0.00
- s006, sheep (Position 4) is 0.00
- s008, Duck is -13.85
The location of each product can be in a different location for each account, as seen by the sheep product for S001,S005,S007 where it appears at positions 4, 3 and 7 respectively.
There are currently 13 individual 'animal' products that can be selected by the user and currently in the DB there are around 50 variations of product strings that exist.
There are currently around 2,500 rows of records that I am needing to report on with this criteria, but the DB table contains around 1.9 million records (the remaining records have a level of consistency that I can work with), it is the remaining 2,500 that have a odd format.
Using the substring_index in MySQL I can split the string based on the delimitator to then have a column for each of the individual data items:
Product
case when 0<= LENGTH(options) - LENGTH(REPLACE(options, '|', '')) then
SUBSTRING_INDEX(SUBSTRING_INDEX(options, '|', 1), '|', -1) ELSE null end as op1
| Account ID | op1 | op2 | op3 | op4 |
|---|---|---|---|---|
| s001 | Cat | Dog | Cow | Sheep |
I can also carry out the same function on the Prices table to then provide each data item as a separate column.
The issue I am having is that I am unable to find away to attribute the price item to the description of the product item based on its location
I do have the option to use SSIS to carry out transformation on the data, I can also save the data to an SQL-server database if an option is available in that language.