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

Question

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

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

There is little need to do want you want in a set based language. You're looking for a crosstab query which does not currently exist in mysql. Checkout http://stackoverflow.com/questions/15997090/crosstab-view-in-mysql — Keith John Hutchison, Nov 01 '16 at 03:48
You might find researching into returning xml from mysql useful. It's fairly simple to convert xml to json if you want to consume the data in a web browser. https://www.google.com.au/webhp?sourceid=chrome-instant&ion=1&espv=2&ie=UTF-8#q=mysql%20return%20xml — Keith John Hutchison, Nov 01 '16 at 03:50
Simple GROUP_CONCAT, as in @RicardoOrtegaMagaña solution or if you want it in separate columns it can be done, but you should decide on the number of columns in advance. — David דודו Markovitz, Nov 01 '16 at 05:07

score 1 · Answer 1 · edited Nov 01 '16 at 04:26

1

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;

edited Nov 01 '16 at 04:26

Abhishek Ginani

4,511
4
23
35

answered Nov 01 '16 at 03:59

jai dutt

780
6
13

Downvoted. The answer has nothing to do with the question and it nothing more than a technical mumble-jumble. "try some primary keys"? "make a view"? seriously? – David דודו Markovitz Nov 01 '16 at 05:04
i just suggest him to use keys bro and this is the right solution what he want – jai dutt Nov 01 '16 at 05:26
How many primary keys are there for a single table? if we don't have primary key in the table, we can't answer the question? If we don't have CREATE VIEW privilege, we can't answer the question? How many rows does your query returns? – David דודו Markovitz Nov 01 '16 at 05:30
rows depend on data and its just my suggestion to use keys am not forcing anybody. bro why are you r so upset – jai dutt Nov 01 '16 at 05:38
The question about the number of rows reffers to the sample set given in the question. – David דודו Markovitz Nov 01 '16 at 06:06

score 1 · Answer 2 · answered Nov 01 '16 at 09:12

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`

score 0 · Answer 3 · answered Nov 01 '16 at 04:50

0

You might want something like this:

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

answered Nov 01 '16 at 04:50

Ricardo Ortega Magaña

2,403
16
23

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

3 Answers3