0

This is my query: 

SELECT f.FieldID,fd.FieldName, p.ProductID,pd.ProductName, p.Price,p.Stoc, p.IsActive,TextValue
FROM products_products p
LEFT OUTER JOIN products_products_details pd
on pd.ProductID = p.ProductID
LEFT OUTER JOIN fld_chosenvalues f
on f.ProductID = p.ProductID
INNER JOIN fld_fields_details fd
ON f.FieldID = fd.FieldID
WHERE  pd.Locale = "ro-RO" and fd.Locale = "ro-RO"

In the image below , you can see the result of the query : enter image description here

The problem is that I want to put the "Volum", the "Tip inchidere", the "Amabalare paiet", the "Ambalare bax" as a column and assign it the value "212ml"... you can see in the image below what exactly i want :

enter image description here

I apologize for the images, but I didnt know any other ways to explain that. Thx

This is the query with pivot, obviously is not working :

SELECT f.FieldID,fd.FieldName, p.ProductID,pd.ProductName, p.Price,p.Stoc, p.IsActive,TextValue
FROM products_products p
LEFT OUTER JOIN products_products_details pd
on pd.ProductID = p.ProductID
LEFT OUTER JOIN fld_chosenvalues f
on f.ProductID = p.ProductID
INNER JOIN fld_fields_details fd
ON f.FieldID = fd.FIeldID
PIVOT
(
    FOR [f.FieldName] IN (['Volum'], ['Tip inchidere'], ['Amabalare palet'], ['Ambalare bax'])
) as pvt;
WHERE  pd.Locale = "ro-RO" and fd.Locale = "ro-RO"
Attila Naghi
  • 2,535
  • 6
  • 37
  • 59
  • 1
    This is called "pivoting". Some databases have built-in functions for this, some require you to do extra work in the query. Please specify the RDBMS so we can give you an appropriate answer. – Barmar Jun 26 '14 at 08:12
  • @Barmar looks like the screenshot is phpMyAdmin/MySQL to me – SchmitzIT Jun 26 '14 at 08:22
  • yes it is in phpmyadmin. if you want I can give you pictures from the structure – Attila Naghi Jun 26 '14 at 08:29
  • possible duplicate of [MySQL pivot table](http://stackoverflow.com/questions/7674786/mysql-pivot-table) – Barmar Jun 26 '14 at 08:31
  • this is a first time when i header about pivot, so i tried it, i updated my post, please check it – Attila Naghi Jun 26 '14 at 08:44

1 Answers1

0

Maybe something like this:

SELECT 
    p.ProductID,
    pd.ProductName,
    SUM(p.Price) AS Price,
    SUM(p.Stoc) AS Stoc,
    p.IsActive,
    MAX(CASE WHEN fd.FieldName='Volum' THEN TextValue ELSE NULL END) AS Volum,
    MAX(CASE WHEN fd.FieldName='Tip inchidere' THEN TextValue ELSE NULL END) AS TipInchidere,  
    MAX(CASE WHEN fd.FieldName='Amabalare paiet' THEN TextValue ELSE NULL END) AS AmabalarePaiet,
    MAX(CASE WHEN fd.FieldName='Ambalare bax' THEN TextValue ELSE NULL END) AS Ambalarebax
FROM 
    products_products p
    LEFT JOIN products_products_details pd
        on pd.ProductID = p.ProductID
    LEFT JOIN fld_chosenvalues f
        on f.ProductID = p.ProductID
    INNER JOIN fld_fields_details fd
        ON f.FieldID = fd.FieldID
WHERE  
    pd.Locale = 'ro-RO' 
    and fd.Locale = 'ro-RO'
GROUP BY
    p.ProductID,
    pd.ProductName,
    p.IsActive
Arion
  • 31,011
  • 10
  • 70
  • 88