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
0 answers
How to select value from column based on value in aggregate column?
I have a query showing inventory levels in the warehouse and I want to see the most recent movement date for all warehouse locations that do not have any inventory.
This is a snippet of my current code
SELECT
WHS_ID
, itemid
,…
akroeker
- 21
- 4
2
votes
1 answer
How can I implement Dijkstra's Algorithm in T-SQL to find the shortest route through my data-points?
I have the following table containing n bins in a warehouse and their coordinate points on a map.
I would like to find the distance between every point in the list.
I have attempted to use a pivot, cross apply, cross join, etc and ultimately get…
ayecob
- 37
- 5
2
votes
1 answer
How to unpivot multiple columns in PySpark?
The columns CGL, CPL, EO should become Coverage Type, the values for CGL, CPL, EO should go in column Premium, and values for CGLTria,CPLTria,EOTria should go in column Tria Premium.
declare @TestDate table (
QuoteGUID…
Gaurav Kumar
- 21
- 1
- 3
2
votes
1 answer
Dynamic SQL to Unpivot Data using Cross Apply with Multiple Columns
Microsoft SQL Server Management Studio v18.8
I have a table that will have varying columns and column names. I need to unpivot the data so I can then eventually store it into a different table. Unfortunately, this is the process since the original…
MonkeyMonkey
- 150
- 1
- 1
- 13
2
votes
1 answer
How to get complex JSON string into columns SQL Server
I have stored some data in the table as JSON string like below.
[
{
"firstName":"John",
"lastName":"Smith",
"age":25,
"Address":{
"streetAddress":"21 2nd Street",
"city":"New York",
…
user2837480
- 349
- 2
- 18
2
votes
1 answer
SQL Dynamically Split Values into new columns
Have a table that contains different IP values for objects separated by commas. Looking for way to split them dynamically into new columns. For example,
10.12.11.20, 192.168.1.3, 192.168.1.4
192.168.1.50,
Based on the results returned in the table…
Sling4778
- 21
- 4
2
votes
3 answers
OUTER/CROSS APPLY Subquery without FROM clause
Most online documentation or tutorials discussing OUTER|CROSS APPLY describe something like:
SELECT columns
FROM table OUTER|CROSS APPLY (SELECT … FROM …);
The subquery is normally a full SELECT … FROM … query.
I must have read somewhere that the…
Manngo
- 14,066
- 10
- 88
- 110
2
votes
1 answer
Speeding up Sql query using CROSS APPLY() eliminating ROW_NUMBER()
Goal:
I want to speed up a sql query of about a million rows of transaction data (order data). I've been able to reduce the time from 50 minutes (using temp tables) to 9 minutes using CROSS APPLY() (see query below). Is there a way I can…
Brent
- 196
- 7
2
votes
2 answers
Cross apply on columns on SQL server. Syntax error near )
I am trying to unpivot several columns, but I can't find the way of solving the syntax error.
It says incorrect syntax near ')'.
Here is the code:
SELECT dates, times, locations, events
FROM mytable
CROSS APPLY
(VALUES ('instance1',…
zhivab
- 99
- 11
2
votes
1 answer
SQL Cross Apply with three tables
I am trying to combine 3 tables using Cross Apply in a time-efficient manor. I can get the results that I want, but the run time is too great. The three tables are:
-CUSTOMERS, which has the columns CustomerId(primary key) and…
TS-
- 317
- 3
- 15
2
votes
0 answers
SQL Server CROSS APPLY never ends with reference to another table
I'm trying to use this ExplodeDate function.
But I dont't get it run with one easy query. I can reproduce this with an empty database on SQL Server 2016 locally and in an Azure Database.
Please use following code to reproduce the issue.
Code for…
Daniel C.
- 569
- 2
- 7
- 19
2
votes
1 answer
2
votes
1 answer
Query XML with nested nodes on Cross Apply
Given a XML structured like this:
1
This is a folder
…
Fabio L.
- 141
- 2
- 12
2
votes
1 answer
Cross Apply yields nulls
I'm quoting MS:
CROSS APPLY returns only rows from the outer table that produce a
result set from the table-valued function.
This would mean that it would not return rows with null values, right?
However, my query is:
select…
George Menoutis
- 6,894
- 3
- 19
- 43
2
votes
1 answer
Cross apply not performant, convert to unpivot (or other)?
Cross apply is very slow when inserting large data sets, I think unpivot (or something else) might be more efficient but I can't figure out how to do it other than by using cross apply:
CREATE TABLE LocData
(Airport varchar(5), City varchar(5),…
baileyswalk
- 1,198
- 2
- 17
- 29