Find Jobs
Hire Freelancers

Code MySQL VIEW that DYNAMICALLY transposes a table with a single pivot

£10-20 GBP

Terminado
Publicado hace más de 7 años

£10-20 GBP

Pagado a la entrega
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.
ID del proyecto: 12255959

Información sobre el proyecto

5 propuestas
Proyecto remoto
Activo hace 7 años

¿Buscas ganar dinero?

Beneficios de presentar ofertas en Freelancer

Fija tu plazo y presupuesto
Cobra por tu trabajo
Describe tu propuesta
Es gratis registrarse y presentar ofertas en los trabajos
Adjudicado a:
Avatar del usuario
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 en 0 día
4,9 (28 comentarios)
4,7
4,7
5 freelancers están ofertando un promedio de £49 GBP por este trabajo
Avatar del usuario
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.
£18 GBP en 1 día
0,0 (0 comentarios)
0,0
0,0

Sobre este cliente

Bandera de UNITED KINGDOM
Horley, United Kingdom
5,0
8
Forma de pago verificada
Miembro desde abr 22, 2016

Verificación del cliente

¡Gracias! Te hemos enviado un enlace para reclamar tu crédito gratuito.
Algo salió mal al enviar tu correo electrónico. Por favor, intenta de nuevo.
Usuarios registrados Total de empleos publicados
Freelancer ® is a registered Trademark of Freelancer Technology Pty Limited (ACN 142 189 759)
Copyright © 2024 Freelancer Technology Pty Limited (ACN 142 189 759)
Cargando visualización previa
Permiso concedido para Geolocalización.
Tu sesión de acceso ha expirado y has sido desconectado. Por favor, inica sesión nuevamente.