i think it satisfy yours requirement

select d1.[type],'Code'= substring(

(select (', '+d.code) from

( SELECT

p.Prefix

,p.Code

,'Type'=

case when...