Code :
SELECT a.name_user, SUBSTRING(( SELECT ', ' + b.number_phone FROM @phone b WHERE b.id_user = a.id_user FOR XML PATH(''), TYPE ).value('.','varchar(max)'), 2, 9999) number_phone FROM @user a ;With ctePhone (id_user, number_phone, rn, rnR) AS ( SELECT a.name_user , b.number_phone , ROW_NUMBER() OVER (PARTITION BY a.name_user ORDER BY a.name_user, b.number_phone) rn , ROW_NUMBER() OVER (PARTITION BY a.name_user ORDER BY a.name_user DESC, b.number_phone DESC) rnR FROM @user a LEFT JOIN @phone b ON b.id_user = a.id_user ), cteRec (id_user, number_phone, rn, rnR) AS ( SELECT id_user, CONVERT(varchar(max),number_phone) number_phone, rn, rnR FROM ctePhone WHERE rn = 1 UNION ALL SELECT a.id_user, CONVERT(varchar(max),b.number_phone + ', ' + a.number_phone) number_phone, a.rn, a.rnR FROM ctePhone a JOIN cteRec b ON b.id_user = a.id_user AND a.rn = b.rn + 1 ) SELECT id_user, number_phone FROM cteRec WHERE rnR = 1 ORDER BY id_user
|