CODEWITHSUNDEEP
mysqlsql
Nicolas Franco
Nicolas Franco

Reputation: 21

Show in just one row all records that have the same id.

Lets say I have a table of product price history, which is the price and product id, with the following records: 

id   price 

1     23     
2     14    
2     23      
2     20  
3     30  
3     40

what I want is to show the data grouped by id, showing the prices at which has been sold each product.

What i expectis something like this: 

id   priceA PriceB PriceC

1    23     NULL   NULL        
2    14     23     20     
3    30     40     NULL

Upvotes: 1

Views: 1435

Answers (3)

Paul Maxwell
Paul Maxwell

Reputation: 35583

This requirement really a bad fit for SQL, but it can be achieved with a lot of fiddling involving "dynamic sql" and fudges to achieve te equivalent of row_number(). i.e. It would be easier to achieve with CTE and row_number() perhaps if MySQL gets bith this could be revisited.

Anyway, what is required is getting the prices into numbered columns, so the first price of each product goes in the first column, the second price in the second column and so on. So we need in the first instance a way to number the rows which will later be transformed into columns. In MySQL this can be done by using variables, like this:

              select
                  @row_num := IF(@prev_value = p.id, @row_num+1, 1) AS RowNumber
                , id
                , price
                , @prev_value := p.id
              from (select distinct id, price from pricehistory) p
              CROSS JOIN ( SELECT @row_num :=1,  @prev_value :='' ) vars
              order by id, price

So that snippet is used twice in the following. In the upper part it forms a set of case expressions that will do the transformation. I the lower part we combine those case expressions with the remainder of the wanted sql and then execute it.

set @sql = (
SELECT GROUP_CONCAT(col_ref)
      FROM (
            select distinct
      concat(' max(case when RowNumber=',RowNumber,' then Price else NULL end) as c',RowNumber)  col_ref
            from (
                  select
                      @row_num := IF(@prev_value = p.id, @row_num+1, 1) AS RowNumber
                    , id
                    , price
                    , @prev_value := p.id
                  from (select distinct id, price from pricehistory) p
                  CROSS JOIN ( SELECT @row_num :=1,  @prev_value :='' ) vars
                  order by id, price
                ) d
            order by `RowNumber`
            ) dc
      );

set @sql = concat('select id,', @sql,
                  ' from  (
                  select
                      @row_num := IF(@prev_value = p.id, @row_num+1, 1) AS RowNumber
                    , id
                    , price
                    , @prev_value := p.id
                  from (select distinct id, price from pricehistory) p
                  CROSS JOIN ( SELECT @row_num :=1,  @prev_value :='''' ) vars
                  order by id, price
                  ) d
                  Group By `id`');

#select @sql
PREPARE stmt FROM @sql;
EXECUTE stmt;
\\

The result of this, based on the sample given is:

    id  c1      c2      c3
1   1   23      NULL    NULL
2   2   14      20      23
3   3   30      40      NULL

This solution can be tested and re-run at: http://rextester.com/AYAA36866

Note the fully generated sql reads like this:

select id
, max(case when RowNumber=1 then Price else NULL end) as c1
, max(case when RowNumber=2 then Price else NULL end) as c2
, max(case when RowNumber=3 then Price else NULL end) as c3
from  (
                  select
                      @row_num := IF(@prev_value = p.id, @row_num+1, 1) AS RowNumber
                    , id
                    , price
                    , @prev_value := p.id
                  from (select distinct id, price from pricehistory) p
                  CROSS JOIN ( SELECT @row_num :=1,  @prev_value :='' ) vars
                  order by id, price
       ) d
Group By `id`

Upvotes: 1

Ricardo Ortega Magaña
Ricardo Ortega Magaña

Reputation: 2442

You might want something like this:

SELECT id, GROUP_CONCAT(string SEPARATOR ' ') FROM priceHistory GROUP BY id;

Upvotes: 0

jai dutt
jai dutt

Reputation: 790

This is not the right way to do things you should use a separate table and try some primary keys.

suppose you have a poductprice table with id and price make a view like 

 CREATE VIEW history AS ( 
  SELECT
    id,
    CASE WHEN id = "1" THEN price END AS priceA,
    CASE WHEN id =  "2" THEN price END AS priceB,
    CASE WHEN id = "3" THEN price END AS priceC
  FROM productprice
);

SELECT * FROM history;

Upvotes: 1

Related Questions