Duplicate ID in SQL select

Question

The select in mysql is this:

SELECT v.product_id, 
       nome            AS Produto, 
       presentation    AS Descricao, 
       presentation    AS Descricao2, 
       name1           AS categoria, 
       description_two AS descricao, 
       price           AS preco, 
       quantity        AS estoque, 
       width, 
       height, 
       depth, 
       weight, 
       name, 
       referencecode, 
       datapostagem 
FROM   variant v 
       INNER JOIN productcategory p 
               ON v.product_id = p.product_id 
       INNER JOIN product 
               ON product.id = p.product_id 
       INNER JOIN category 
               ON category.id = category_id 
       INNER JOIN image i 
               ON i.product_id = p.product_id 
       INNER JOIN descriptiongroup D 
               ON D.product_id = p.product_id 
       INNER JOIN stock S 
               ON S.variant_id = v.id 
       INNER JOIN dimensiongroup G 
               ON G.variant_id = v.id 
LIMIT  10 

The result return lot of duplicate product_id, how can I separate the product_id in another column? Like product2, product3, product4 ?

If I do that select it shows me the correct form but just for one result, how can I join this select below in my main select?

 select 
     product_id,
     sum(case when presentation = 'Unitário = R$ 11,90 ' THEN price END) product_id1,
     sum(case when presentation = '5 Pe?as = R$ 5,00 cada' THEN price END) product_id2,
     sum(case when presentation = 'Bluesky Todas' THEN price END) product_id3
 from 
     Variant
 where 
     product_id = 1604

The result is:

        product_id  product_id1     product_id2     product_id3     
        1604    11.9    25  15

How can I make it dynamic, to show me all results not just for one?

The image of result below:

enter image description here

Answer 1

As mentioned in the comments above, following the guidelines in https://stackoverflow.com/help/mcve would help others to help you.

What I can suggest from what you have told us is that this is a pivot-table or crosstab problem: you have product ids in rows and you want to see them in columns.

Excel does this well because it is largely a presentation tool. Tableau and Qlik do them too. The easiest thing is to select the data you want (as you have done), copy it into your presentation tool and do the cross tab there. MySQL, like the other credible relational database systems manage data; you should use some sort of presentation tool to present it.

Some databases (such as PostgreSQL and Microsoft SQL Server) have limited cross tab functionality in their command line tools but I never use them.

Pivot tables in SQL are possible, but really clumsy, because that is not what SQL is for. You can see more about this at: http://www.thedatastudio.net/ugly_cross_tab.htm.

The bottom line is: use a presentation tool instead.