Question 112972 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Get database column into rows with duplicate data

I’ve mysql database where data is column:

+----+-------+--------+--+
| ID | refID |  data  |  |
+----+-------+--------+--+
|  1 |  1023 | aaaaaa |  |
|  2 |  1024 | bbbbbb |  |
|  3 |  1025 | cccccc |  |
|  4 |  1023 | ffffff |  |
|  5 |  1025 | gggggg |  |
|  6 |  1022 | rrrrrr |  |
+----+-------+--------+--+

I want this data to be shown in rows with duplicate values:

+----+-------+--------+--------+
| ID | refID |  data  | data2  |
+----+-------+--------+--------+
|  1 |  1023 | aaaaaa | ffffff |
|  2 |  1024 | bbbbbb |        |
|  3 |  1025 | cccccc | gggggg |
|  4 |  1022 | rrrrrr |        |
+----+-------+--------+--------+

Is it possible with PHP & MYSQL?

I tried mysql query group by refID but it’s not working.

Tags:

2

Answers


  1. 0

    Assuming there be at most 2 duplicates per refID, we can try using aggregation:

    SELECT
    refID,
    MIN(data) AS data,
    CASE WHEN MIN(data) <> MAX(data) THEN MAX(data) END AS data2
FROM yourTable
GROUP BY refID
ORDER BY refID;

    Your expected ID column seems redundant, if you really need it then just add ROW_NUMBER() OVER (ORDER BY some_col) to the above query.

    Login or Signup to reply.
  2. 0

    No, I’ve only 3 data categories

    You could use row_number in a subquery to give each refID group a specific number and then apply conditional aggregation in an outer query:

    select min(ID) as ID,
       refID,
       max(case when rn = 1 then data end) as data1 ,
       max(case when rn = 2 then data end) as data2 ,
       max(case when rn = 3 then data end) as data3 
from ( select ID,
       refID,
       data,
       row_number() over(partition by refID order by ID asc) as rn
       from test_tbl
     ) tbl
group by refID;

    Consider the following data:

    CREATE TABLE test_tbl  (
  ID int,
  refID int ,
  data varchar(10)  );

insert into test_tbl values
(1,1023,'aaaaaa'),
(2,1024,'bbbbbb'),
(3,1025,'cccccc'),
(4,1023,'ffffff'),
(5,1025,'gggggg'),
(6,1022,'rrrrrr'),
(7,1023,'eeeeee');

    Result:

    ID    refID   data1   data2   data3
6     1022    rrrrrr  null    null
1     1023    aaaaaa  ffffff  eeeeee
2     1024    bbbbbb  null    null
3     1025    cccccc  gggggg  null

    https://dbfiddle.uk/3LpgXlb9

    Note, ID seems redundant to me and it can be removed from the select statements

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search