SELECT temp.id, temp.name, temp.description,r.cnt,r.cusr
FROM calibration_template ct
CROSS APPLY
(
SELECT COUNT(*) cnt , STUFF
((
SELECT CONCAT(', ' ,a.id)
FROM deprec a
WHERE ( a.cal_template_id = d.id )
FOR XML PATH('')
) ,1,2,'')
AS cusr
FROM deprec d
WHERE d.cal_template_id = ct.id
)r
SELECT temp.id, temp.name, temp.description,r.cnt,r.cusr
FROM calibration_template ct
CROSS APPLY
(
SELECT COUNT(*) cnt , STUFF
((
SELECT ', '+CAST(a.id AS VARCHAR(10))
FROM deprec a
WHERE ( a.cal_template_id = d.id )
FOR XML PATH('')
) ,1,2,'')
AS cusr
FROM deprec d
WHERE d.cal_template_id = ct.id
)r
--
--
oa1.count,
oa2.ids
FROM calibration_template temp
OUTER APPLY (
SELECT COUNT(*)
FROM deprec
WHERE deprec.cal_template_id = temp.id
) AS oa1(count)
OUTER APPLY (
SELECT STUFF(ids, 1, 1, '')
FROM (
SELECT ',' + CAST(deprec.id AS varchar(10))
FROM deprec
WHERE deprec.cal_template_id = temp.id
ORDER BY deprec.id FOR XML PATH('')
) AS derived(ids)
) AS oa2(ids)