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
2
votes
1 answer
Getting values from a table that's inside a table (unpivot / cross apply)
I'm having a serious problem with one of my import tables. I've imported an Excel file to a SQL Server table. The table ImportExcelFile now looks like this…
diiN__________
- 7,393
- 6
- 42
- 69
2
votes
1 answer
Why use cross Apply to get the values in XML?
Few days ago, i tried the Extended Events for replace the SQL Server profiler.
Then i wanted to put the xel files generated in the sql server database with sql.
What i think odds, it's a lot of site use the function nodes with a Cross Apply to get…
MoonLightFlower
- 173
- 1
- 14
2
votes
1 answer
Preferred way to access data within XML columns in SQL Server
Background
Recently I've started to use XML a lot more as a column in SQL Server 2005. During a bit of downtime yesterday, I noticed that two of the link tables I used a really just in the way and it bores me to tears having to write yet more…
Tom
- 3,354
- 1
- 22
- 25
2
votes
1 answer
SQL - CROSS APPLY not working properly
I think I'm losing my mind here. I try to update a specific subset of Table1. Let's say the SELECT from Table1 returns 1 million rows. For each of these rows, I want to cross apply a specific calculated sum from Table2.
I know for a fact that not…
Koruba
- 173
- 1
- 9
2
votes
2 answers
SQL Server: Pulling updated data from a function during a CROSS APPLY
Quick Summary: I have a function that pulls data from table X. I'm running an UPDATE on table X, and using a CROSS APPLY on the function that is pulling data from X (during the update) and the function doesn't look to be returning updated data.
The…
PatrickSteele
- 14,489
- 2
- 51
- 54
2
votes
1 answer
Cross Apply vs "comma Join"
I had a co-worker that loved using what she referred to as a "comma join". I have recently used cross apply for the first time ever in a live environment and noticed that it gave the same result as her "comma join" and was wondering if there was…
Chad Portman
- 1,134
- 4
- 12
- 38
2
votes
1 answer
SQL get comma separated values of a column from XML
I am calling Scalar UDF from a stored procedure to get a column value.
Inside the scalar UDF I have an xml and I have to get the comma separated values of a particular node.
I am using Cross Apply but this is causing huge performance headache…
Ravish Kumar
- 67
- 1
- 6
2
votes
3 answers
Generate Numbers between Range
Suppose I have 2-6 and by using the below program I am able to generate the range
DECLARE @range VARCHAR(10) = '2-6'
DECLARE @startRange INT = PARSENAME(REPLACE(@range, '-', '.'), 2)
DECLARE @lastRange INT = PARSENAME(REPLACE(@range, '-', '.'),…
priyanka.sarkar
- 25,766
- 43
- 127
- 173
2
votes
1 answer
SQL Pivot after Cross Apply
I am trying to do a pivot in SQL Server wherein I need to pivot the following table:
key value
column1 billy
column2 billy@billy.com
column5 NULL
column6 false
column9 true
I want the values in the "key" column to be the column…
SUMguy
- 1,505
- 4
- 31
- 61
2
votes
2 answers
Mutiple columns with independent where clause - SQL Pivot?
Is it possible to take a table that is structured in the following fashion:
ID Month Info1 Info2
1 1 A B
1 2 C D
1 3 E F
2 3 G H
2 4 I J
That ends up…
James F
- 535
- 9
- 26
2
votes
2 answers
SQL Server JOIN/APPLY Query
What join should I use to get my desired output base on dummy table below
DETAIL TABLE
x------------------------------x
| empID | empName | date |
x------------------------------x
| 1 | emp1 | 10/01/2016 |
| 1 | emp1 | 10/03/2016…
Devs
- 65
- 8
2
votes
1 answer
Optimizing/Alternate to Cross Apply
I need help in optimizing following SQL query.
Table 1 : 62000 records
Col 1 Col 2 Col 3
1 X, Y A, B
2 L, M, N P, Q, R
3 G H
Table 2 : 2000 records
Col 1 Col 2
1 One
2 Two
Output…
Kushal Arora
- 31
- 8
2
votes
1 answer
Consolidating multiple Cross Applies into one query
I have a table (Data) that includes nine long text fields. It currently has 1,000,000 records and it's growing, so I would like to speed things up a bit.
I'm using a Table-Valued RegEx function (master.dbo.RegExMatches) to parse out words from these…
rsjaffe
- 5,600
- 7
- 27
- 39
2
votes
1 answer
MS Access CROSS APPLY analogue
Having a table with devices tracks. Each tracking device has a unique ID, and users upload their GPS tracks to the site, specifying the tracking device ID. This data is stored in a table, so each row is composed from DeviceID, Lat, Lon, and Date…
sindilevich
- 398
- 5
- 9
2
votes
1 answer
Cross apply scope is not visible?
Currently I have a udf which returns a table. it returns 3 rows . Each row return the parameter's value *10
Something like (pseudo):
ALTER FUNCTION [dbo].[myUdf]
(
@num int
)
RETURNS @myTable TABLE (h int )
AS
begin
insert into…
Royi Namir
- 144,742
- 138
- 468
- 792