Cannot consolidate rows when SELECTING from 4 tables

You can get the result formatted in TSQL but it is not a good practice. If the result is what you want, then format it in your front end.