So, it's been a year- but I needed to run this again so I revisited it. I ended up with:
SELECT
(
CASE WHEN(
table Name.Carer2 = ''
) THEN table Name.Carer1 WHEN(
table Name.Carer1 = ''
) THEN table Name.Carer2 ELSE CONCAT_WS(
' & ',
table Name.Carer1,
table Name.Carer2
)
END
) AS Carers,
(
CASE WHEN(
table Name = ''
) THEN table Name.Carer1_Contact WHEN(
table Name.Carer1_Contact = ''
) THEN table Name.Carer2_Contact ELSE CONCAT_WS(
',',
table Name.student_info.Carer1_Contact,
table Name.Carer2_Contact
)
END
) AS Contact,
GROUP_CONCAT(
table Name.First_Name SEPARATOR ' & '
) AS Children
From Table Name
Group Bytable Name.Carer1_Contact,
table Name.Carer2_Contact
I'm now doing some more work to this to make it even better and I've come across another stumbling block.