3

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.

1
  • See tag [pivot].
    – Rick James
    Sep 23, 2022 at 19:09

2 Answers 2

0
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;  
2
0

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

1
  • Thanks, that did the trick. I guess because it is an aggregation it does not stop at the first match, is it correct ?
    – dbase964
    Sep 26, 2022 at 13:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.