Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Help with SQL
Posted on 2016-09-27
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
4-
3
2
9 Comments
Active today
SELECT temp.id, temp.name, temp.description,
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)
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)
Active today
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
Active today
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
--
Active today
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)
Active today
@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.
Featured Post
Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Why is this different from all of the other step by step guides? Â Because I make a living as a DBA and not as a writer and I lived through this experience.
Defining the name:
When I talk to people they say different names on this subject stuff l…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month5 days, 9 hours left to enroll
626 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.