0

I've looked at similar posts (specifically MySQL - Rows to Columns), and from that wrote the MYSQL statement below in an effort to correctly transpose rows to columns. The transposition works for the most part, but only one IT.TaxStatus is selected and given for each row (that is for each IT.ClientCode), and I cannot for the life of me figure out why.

SELECT IT.ClientCode,
CASE WHEN IT.TaxYear='2021' THEN IT.TaxStatus ELSE NULL END AS '2021', 
CASE WHEN IT.TaxYear='2020' THEN IT.TaxStatus ELSE NULL END AS '2020', 
CASE WHEN IT.TaxYear='2019' THEN IT.TaxStatus ELSE NULL END AS '2019', 
CASE WHEN IT.TaxYear='2018' THEN IT.TaxStatus ELSE NULL END AS '2018' 
FROM IT GROUP BY IT.ClientCode 
Order by IT.ClientCode;

My original table: Original table

Expected result: Expected Result

Do anyone have an explanation why only one IT.TaxStatus per IT.ClientCode would be given in the final recordset, and not all the years?

Thank you for your assistance!

Regards Wessel

Wessel Steyl
  • 11
  • 3

1 Answers1

1

You need to add an aggregation function

SELECT IT.ClientCode,
MAX(CASE WHEN IT.TaxYear='2021' THEN IT.TaxStatus ELSE NULL END) AS '2021', 
MAX(CASE WHEN IT.TaxYear='2020' THEN IT.TaxStatus ELSE NULL END) AS '2020', 
MAX(CASE WHEN IT.TaxYear='2019' THEN IT.TaxStatus ELSE NULL END) AS '2019', 
MAX(CASE WHEN IT.TaxYear='2018' THEN IT.TaxStatus ELSE NULL END) AS '2018' 
FROM IT 
GROUP BY IT.ClientCode 
Order by IT.ClientCode;
nbk
  • 45,398
  • 8
  • 30
  • 47
  • nbk, thank you for your answer. I knew about the aggregate functions, but left it out as I thought is was only necessary in the case that numbers need to be added together, or some similar action. Anyway, I added it to my MYSQL statement, and it works flawlessly. For some reason my logic does not follow why it works, but thank you very much for the help! Regards Wessel – Wessel Steyl May 16 '21 at 19:46