Merging multiple rows into one in MySQL

Question

I have the following table :

Column1	Column2	Column3	Column4	Account
One	Two	Three	Data1	Acc1
One	Two	Three	Data2	Acc2
One	Two	Three	Data3	Acc3
Five	Six	Seven	Data3	Acc4
Five	Six	Seven	Data1	Acc1

And I would like to make a query to obtain the following result :

Column 1	Column2	Column3	Data1	Data2	Data3
One	Two	Three	Acc1	Acc2	Acc3
Five	Six	Seven	Acc1	NULL	Acc4

I tried something like :

select Column1, Column2, Column3,
case
    when Column4 = 'Data1' then Account
end as Data1,
case
    when Column4 = 'Data2' then Account
end as Data2,
case
    when Column4 = 'Data3' then Account
end as Data3
from mytable group by Column1, Column2, Column3

But instead I got the following :

Column 1	Column2	Column3	Data1	Data2	Data3
One	Two	Three	Acc1	NULL	NULL
Five	Six	Seven	NULL	NULL	Acc4

It seems that group by stops at the first distinct line, therefore it stops reading lines with One, Two Three once it found one, and of course same with Five, Six, Seven, therefore it only fills the Data columns according to the first match.

How can I get to my desired behaviour ?

Thanks in advance.

Answer 1

declare @table table
(
Column1 varchar(20),
Column2 varchar(20),
Column3 varchar(20),
Column4 varchar(20),
Account varchar(20)
);

insert into @table
values('One','Two','Three','Data1','Acc1'),
('One','Two','Three','Data2','Acc2'),
('One','Two','Three','Data3','Acc3'),
('Five','Six','Seven','Data3','Acc4'),
('Five','Six','Seven','Data1','Acc1');

select * from @table PIVOT  
(  
  max(Account)  
  FOR Column4 IN ([Data1], [Data2], [Data3])  
) AS PivotTable;  

Answer 2

You do not get the expected result because you should use conditional aggregation.

select Column1,
       Column2,
       Column3,
       max(case when Column4 ='Data1' then Account else null end)  as Data1,
       max(case when Column4 ='Data2' then Account else null end)  as Data2,
       max(case when Column4 ='Data3' then Account else null end)  as Data3
from test
group by Column1,Column2,Column3 

Above query takes in consideration only the values Data1,Data2 and Data3 in Column4.

If you want to do this dynamically you could use prepared statement:

SET SESSION group_concat_max_len = 1000000; #this is because of group_concat default max value 

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT CONCAT('max(case when Column4 = ''',Column4,''' 
                                          then Account end) ', Column4)) INTO @sql 
FROM
  test;
SET @sql = CONCAT('SELECT Column1,Column2,Column3 ,', @sql, ' 
                  FROM test 
                   GROUP BY Column1,Column2,Column3');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

https://dbfiddle.uk/ipsHlnQm

There are plenty of examples in this site or others about MySQL pivot, below some links:

MySQL single table static and dynamic pivot

https://ubiq.co/database-blog/how-to-create-dynamic-pivot-tables-in-mysql/

https://stackoverflow.com/questions/12598120/mysql-pivot-table-query-with-dynamic-columns