Code MySQL VIEW that DYNAMICALLY transposes a table with a single pivot
£10-20 GBP
Completed
Posted over 7 years ago
£10-20 GBP
Paid on delivery
We have one table to transpose on a specific pivot. That table's contents is derived from two other tables in the background.
The transpose of rows to columns has to pivot on the product identifier (SKU) and number of rows per SKU is always unknown. BUT the number of columns and their possible contents are derived from another tables (see below).
To explain the schema:
table to pivot:
Name: Feed_SKUs_Attributes
id, int(11) [0]
SKU, int(11)
Attribute , varchar(55)
Value, varchar(255) NULL
variation_attribute, varchar(3) - a yes/no flag
Example data:
columns:
id SKU, Attribute, Value, variation_attribute
data:
439, 2092, Colour, White, yes
440, 2092, Bottle size, Standard (750ml) , yes
54, 2092, ABV , Nonalcoholic, no
It is that Feed_SKUs_Attributes table that needs a view created to have one record per SKU, instead of x (in the above example 3). For example, like this instead:
columns:
SKU, Colour, Colour variation_attribute, Bottle, Bottle variation_attribute, Size, Size variation_attribute, ABV, ABV variation_attribute
data:
2092, White, yes, Standard (750ml), yes, Nonalcoholic, no
All possible attributes are defined in another table, so the number of columns to create is the number of records in that table SKU_Attributes x2 (x2 for each variation_attribute flag)
I don't think this is relevant, but all possible values for the attribute are also set in another table, called SKU_Attribute_Values.
I must re-emphasise that this must be totally dynamic and not depend on hardcoding any attribute column names, etc. The second two tables provide the ammunition for the loops probably needed.
Hi! I propose use this manipulation:
1) group attributes in one string with using group_concat. Like this:
SELECT
GROUP_CONCAT(DISTINCT
CONCAT('GROUP_CONCAT(IF(pr.Attribute= ''', pr.`Attribute`, ''', [login to view URL], NULL)) AS ', pr.`code`)
) INTO @SQL
FROM SKU_Attributes AS pr;
2) Then add previous statement in select. Like this:
SET @SQL = CONCAT('CREATE view viewName as SELECT p.*, ', @SQL, '
FROM Feed_SKUs_Attributes AS p
)
GROUP BY [login to view URL];');
3) PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
But I need to see schema and check this poroposal. Please send me dump of needed tables.
£23 GBP in 0 day
4.9 (28 reviews)
4.7
4.7
5 freelancers are bidding on average £49 GBP for this job
I'm a full time professional web developer having 5+ years of experience in HTML5,
CSS3, jQuery, PHP, mysql, Javascript, Bootstrap, Photoshop, logo designing, shopping
carts and CMSs like Wordpress, & more. From full time websites & plugins development to hourly
bug fixing I work in flexible hours and can be available online everyday on Skype, phone or email
to provide 100% satisfaction and error free nice, professional work.