The T-SQL APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. CROSS APPLY acts as a replacement for an INNER JOIN and only returns rows if there is a match on the join condition. Using CROSS APPLY in some instances will perform better than an equivalent JOIN statement. It is similar to a `CROSS JOIN LATERAL` in the SQL standard
Questions tagged [cross-apply]
358 questions
3
votes
2 answers
transpose columns to row using cross apply
Ori Data
+--------+-------+---------------+------------+--------+--------------+-------+
| RowNum | SeqNo | Name | NameReason | Gender | GenderReason | ID …
user3542587
- 313
- 1
- 4
- 13
3
votes
2 answers
Fixing CROSS APPLY function- one row to multiple
I’ve been trying to find a way to split my rows into multiple rows. I came across a CROSS APPLY example and tried to recreate it. I was able to do this, however, I cannot get anything to filter from the WHERE clause. The code looks messy, so I am…
KMR
- 49
- 6
3
votes
1 answer
Fix cross applying rows when records are duplicated
I have an EVENT table which contains enter and exit events. I calculate work time with this procedure:
SET @worktime = (SELECT SUM(mins)
FROM
(SELECT
entry.EmployeeId, entry.DateTime AS…
fanarek
- 367
- 3
- 16
3
votes
1 answer
Get all rows, even when node is not always present
I have thousands of xml files to get data from.
To achieve that I have used the cross apply method.
But the problem is, some nodes are not always present in the xml-file.
In my example this is the node 'valueX' (inside the product node). And this…
gbr
- 33
- 3
3
votes
1 answer
Split data totals by months
I'm working on a report and started off by creating this summary SQL statement:
SELECT o.description,
sum(t.total_minutes) total_minutes,
sum(n.total_new_minutes) total_new_minutes
FROM job i
INNER JOIN
…
Keith Mifsud
- 725
- 5
- 16
3
votes
1 answer
Extract data matching a condition inside an XML array in SQL server
Considering this simple table id (int), name (varchar), customFields (xml)
customFields contains an XML representation of a C# Dictionary. E.g :
-
1 Audi
Johann
- 12,158
- 11
- 62
- 89
3
votes
1 answer
Cross Apply At Different Level
I have xml data that looks like this:
Brazil
Germany
1
7
…
clattenburg cake
- 1,096
- 3
- 19
- 40
3
votes
1 answer
CROSS APPLY versus UNPIVOT
I found that UNPIVOT is able to automatically exclude fields that have NULL values. However, CROSS APPLY-VALUES method is not able to do that. Does anyone know how to automatically exclude NULL value fields from CROSS APPLY-VALUES? Let's say,…
Casperonian
- 176
- 5
- 19
3
votes
1 answer
using xml .nodes() functionality to return all instances
I have the following code but it only returns the first instance of the MonthDate and 'Value' values. I have been experimenting with the .nodes() and Cross Apply but can't figure out the syntax, I keep getting the error of;
Invalid object name…
Nick G
- 1,209
- 8
- 34
- 58
3
votes
1 answer
When to use the table operator APPLY
I'm trying to understand the table operator APPLY.
Here is the example:
CREATE TABLE #y ( Name char(8), hoursWorked int);
GO
INSERT INTO #y VALUES ('jim',4);
INSERT INTO #y VALUES ('michael',40);
INSERT INTO #y VALUES ('raj',1000);
INSERT INTO #y…
whytheq
- 34,466
- 65
- 172
- 267
3
votes
2 answers
Efficient Cross Apply with a CLR integrated table function
In SQL Server, I have a CLR integration based table valued function, GetArchiveImages. I call it something like this:
SELECT ...
FROM Items
CROSS APPLY GetArchiveImages(Items.ID) AS archiveimages
WHERE ...
The issue is that there is overhead for…
Bryce Wagner
- 2,640
- 1
- 26
- 43
3
votes
2 answers
Cross Apply to get child parent value from Xml in SQL Server
I have the following XML:
Paresh
- 39
- 1
- 1
- 2
3
votes
1 answer
Extracting XML data in SQL - too many cross apply statements
I have an xml document containing details from a Statement:
Jacob Danks
- 33
- 1
- 5
3
votes
1 answer
The equivalent of cross apply and select top
I have table (script below):
use master
go
--
--
if db_id ( 'CrossApplyDemo' ) is not null
drop database CrossApplyDemo
go
create database CrossApplyDemo
go
use CrossApplyDemo
go
--
if object_id ( 'dbo.Countries', 'U' ) is not null
drop table…
testCoder
- 7,155
- 13
- 56
- 75
3
votes
4 answers
Cross Apply - LINQ to Objects
In T-SQL you can use CROSS APPLY to get all possible variations between the table left and right from the statement. Now I have the following situation in C# and I hope there is a way to solve my problem using LINQ-to-Objects.
I have a list with…
hwcverwe
- 5,287
- 7
- 35
- 63