Solved

Help with SQL

Posted on 2016-09-27
9
82 Views
Last Modified: 2016-09-29
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
0
Comment
Question by:HLRosenberger
  • 4
  • 3
  • 2
9 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 41818688
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)
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41818951
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


				

Open in new window

0
 
LVL 1

Author Comment

by:HLRosenberger
ID: 41821528
Pawan - I get 'CONCAT' is not a recognized built-in function name.
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 1

Author Comment

by:HLRosenberger
ID: 41821531
Scott  - your method works.  Can you please explain what exactly it does?
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41821557
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

--

Open in new window

--
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 41822008
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)
1
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41822069
@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.
0
 
LVL 1

Author Closing Comment

by:HLRosenberger
ID: 41822279
Thanks so much!
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41822826
@Author - Did you tried my solution I posted.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question