asked on Help with SQL
I have the SQL below. In addition to the count of records that match in deprec, deprec has an ID column. I want a list of all IDs for the records that match, returned in one text column, like '123', '456', '666'). How can I do this? Using a View? A stored proc?
SELECT temp.id, name, description, (SELECT COUNT(*) FROM deprec where cal_template_id = temp.id) as count
FROM calibration_template temp
SELECT temp.id, name, description, (SELECT COUNT(*) FROM deprec where cal_template_id = temp.id) as count
FROM calibration_template temp
Microsoft SQL Server 2008Microsoft SQL Server
Last Comment
Pawan Kumar
Here it is...try
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
ASKER
Pawan - I get 'CONCAT' is not a recognized built-in function name.
ASKER
Scott - your method works. Can you please explain what exactly it does?
Try this..
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
--
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
@Author - Great that it worked. the explanation is below.
Could you please choose one answer as accepted and close the question. Thanks !
As per Microsoft documentation -
The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input
Type of APPLY: CROSS APPLY and OUTER APPLY.
OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function.
Could you please choose one answer as accepted and close the question. Thanks !
As per Microsoft documentation -
The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input
Type of APPLY: CROSS APPLY and OUTER APPLY.
OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function.
ASKER
Thanks so much!
@Author - Did you tried my solution I posted.
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)