[Mssql] group_concat 함수처럼 조회하기

SELECT id, count(*) AS cnt
    , nm = STUFF((
          SELECT ',' + tmp.name
          FROM table tmp
          WHERE tb.id = tmp.id
          GROUP BY tmp.name
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
    , tel = STUFF((
          SELECT ',' + tmp.tel
          FROM table tmp
          WHERE tb.id = tmp.id
          GROUP BY tmp.tel
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM table tb
GROUP BY id
ORDER BY COUNT(*) DESC;