Please help me write a SQL query. I have a table Name with these columns and sample data:
ID FIRSTNAME SURNAME FULLNAME
---------------------------------------------------
1 JOHN PETER JOHN PETER
2 PETER JACKSON PETER JACKSON
3 JOHN PAUL JOHN PAUL
3 JOHN SECONDNAME JOHN SECONDNAME
Fullname is first name + space + last name and ID can repeat.
I want to add another column OTHERNAMES: it will CONCATENATE with ALL firstname and surnames of a person (ID) has with delimiter ;
Table should look like this in the end:
ID FIRSTNAME SURNAME FULLNAME OTHERNAMES
------------------------------------------------------------------------
1 JOHN PETER JOHN PETER JOHN PETER
2 PETER JACKSON PETER JACKSON PETER JACKSON
3 JOHN PAUL JOHN PAUL JOHN PAUL;JOHN SECONDNAME
3 JOHN SECONDNAME JOHN SECONDNAME JOHN PAUL;JOHN SECONDNAME