Get database column into rows with duplicate data-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

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 useless to me and it can be removed from the select statements