Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage
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
Asked:
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
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
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
--
Yes.
An OUTER APPLY is effectively a type of LEFT JOIN (but with additional ways to control the results that are returned). For every row in the left/source table -- in this case "calibration_template", alias "temp" -- each OUTER APPLY is run separately.
Say the first id in temp is 17. Then the first OUTER APPLY does a standard COUNT() of the number of rows where deprec.cal_template_id = 17 (the current value of temp.id).
The second outer apply gets all the deprec.id values that match 17 (the current value of temp.id), while using the XML concatenation technique to combine them into a single string.
After all the APPLYs are processed, the next row is read from the source table, and the APPLY steps are run again for that row. And so on until all rows have been processed.
As a test, let's remove the concatenation from the second apply, and we'll get all the matching ids from deprec returned, but each in their own, separate row:
SELECT temp.id, temp.name, temp.description,
oa1.count,
oa2.id
FROM calibration_template temp
OUTER APPLY (
SELECT COUNT(*)
FROM deprec
WHERE deprec.cal_template_id = temp.id
) AS oa1(count)
OUTER APPLY (
SELECT deprec.id
FROM deprec
WHERE deprec.cal_template_id = temp.id
) AS oa2(id)
An OUTER APPLY is effectively a type of LEFT JOIN (but with additional ways to control the results that are returned). For every row in the left/source table -- in this case "calibration_template", alias "temp" -- each OUTER APPLY is run separately.
Say the first id in temp is 17. Then the first OUTER APPLY does a standard COUNT() of the number of rows where deprec.cal_template_id = 17 (the current value of temp.id).
The second outer apply gets all the deprec.id values that match 17 (the current value of temp.id), while using the XML concatenation technique to combine them into a single string.
After all the APPLYs are processed, the next row is read from the source table, and the APPLY steps are run again for that row. And so on until all rows have been processed.
As a test, let's remove the concatenation from the second apply, and we'll get all the matching ids from deprec returned, but each in their own, separate row:
SELECT temp.id, temp.name, temp.description,
oa1.count,
oa2.id
FROM calibration_template temp
OUTER APPLY (
SELECT COUNT(*)
FROM deprec
WHERE deprec.cal_template_id = temp.id
) AS oa1(count)
OUTER APPLY (
SELECT deprec.id
FROM deprec
WHERE deprec.cal_template_id = temp.id
) AS oa2(id)
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
@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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008
From novice to tech pro — start learning today.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
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)