Mine

SELECT DISTINCT did||'c' AS did,TO_HEX(d.amrid) ||', '||d.amrid||', Channel '||d.channel||','||
tid,CASE WHEN tid=1 THEN 'Commercial' WHEN tid=0 THEN 'Residential' ELSE '' END||
COALESCE(', ' || d.bid, '') AS name FROM devices d WHERE tid=1 ORDER BY did;