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
8
votes
2 answers
Crazy SQL question: How to do a sort of cross apply with a pivot?
Customer has shortcuts in their data where they have a quantity of records in a field. When I parse them, I need to manufacture records, one for each quantity, incrementing the "identifier" by 7 days (because the number represents a date.Example: a…
Zachary Scott
- 20,968
- 35
- 123
- 205
7
votes
1 answer
How to unpivot columns using CROSS APPLY in SQL Server 2012
I want to use CROSS APPLY to UNPIVOT multiple columns.
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…
Serdia
- 4,242
- 22
- 86
- 159
7
votes
2 answers
Why does window functions not work in CROSS APPLY?
There is a simple code. I always thought that both outside ROW_NUMBER and the one in CROSS APPLY clause are supposed to generate the same output (in my example I excepct rn = crn). Could you please explain why isn't it like that?
CREATE TABLE #tmp (…
Dmitrij Kultasev
- 5,447
- 5
- 44
- 88
7
votes
2 answers
Why cross apply is faster than inner join?
I have the following functions:
FunctionA - returns Object ID and Detail ID
FunctionB - returns Detail ID and Detail Name
The following query is used to extract Object ID, Detail ID and Detail Name:
SELECT FunctionA.ID
,FunctionA.DetailID
…
gotqn
- 42,737
- 46
- 157
- 243
6
votes
2 answers
simplify SQL statement by using CTE
I have a query like the following:
SELECT A.a, A.b, B.c,
(CASE WHEN ... THEN ... ELSE ... END) AS CalculatedValue,
B.d
FROM dbo.TableA A INNER JOIN
dbo.TableB B ON (...)
WHERE (CASE WHEN ... THEN ... ELSE ... END) BETWEEN @DayStart AND…
Davide Piras
- 43,984
- 10
- 98
- 147
6
votes
2 answers
Is there any difference between CROSS APPLY and OUTER APPLY when creating a cartesian product?
When creating a cartesian product between two tables, is there any difference between CROSS APPLY and OUTER APPLY?
This may seem like a silly question given that without a relationship expressed between the tables, the right-hand table can't fail to…
T.J. Crowder
- 1,031,962
- 187
- 1,923
- 1,875
6
votes
2 answers
How to rewrite CROSS APPLY to INNER JOIN to make the view indexed
On a separate thread I got a working example on how to translate my stored proc to a view, that will hold customer names to orders mapping, where orders are comma-separated lists of orders, including NULL for no orders. So for the table below, I…
kateroh
- 4,382
- 6
- 43
- 62
6
votes
1 answer
TSQL Multiple column unpivot with named rows possible?
I know there are several unpivot / cross apply discussions here but I was not able to find any discussion that covers my problem. What I've got so far is the following:
SELECT Perc, Salary
FROM (
SELECT jobid, Salary_10 AS Perc10, Salary_25 AS…
ksauter
- 112
- 1
- 7
6
votes
2 answers
Join tables by column names, convert string to column name
I have a table which store 1 row per 1 survey.
Each survey got about 70 questions, each column present 1 question
SurveyID Q1, Q2 Q3 .....
1 Yes Good Bad ......
I want to pivot this so it reads
SurveyID Question Answer
1 Q1 …
PeddiePooh
- 403
- 8
- 17
5
votes
1 answer
CROSS APPLY a FREETEXTTABLE
MS SQL Server 2005: table1 has a full text index. I want to run multiple freetexttable searches against it in a single query, but the two attempts i have fail. any help would be appreciated, thanks! p.s. am willing to upgrade to sql 2008 if it…
user433342
- 859
- 1
- 7
- 26
5
votes
2 answers
TSQL - extend query consisting of cross apply and pivot
this question is based on my Previous Question.
I need to extend the query so that I can incorporate two other tables (running on another server instance).
In this Fiddle I added those two tables:
CREATE TABLE LookUp
([docID] varchar(10),…
Tony Clifton
- 703
- 3
- 14
- 27
5
votes
1 answer
Convert T-SQL Cross Apply to Oracle
I'm looking to convert this SQL Server (T-SQL) query that uses a cross apply to Oracle 11g. Oracle does not support Cross Apply until 12g, so I have to find a work-around. The idea behind the query is for each Tab.Name that = 'Foobar', I need find…
Brett
- 51
- 4
5
votes
3 answers
SQL Server cross apply not working?
http://sqlfiddle.com/#!3/78273/1
create table emptb1
(
id int,
name varchar(20),
dept int
)
insert into emptb1 values (1,'vish',10);
insert into emptb1 values (2,'vish',10);
insert into emptb1 values (3,'vish',30);
insert into emptb1 values…
Registered User
- 1,554
- 3
- 22
- 37
4
votes
3 answers
CROSS APPLY issue - filtering a many-to-one relationship
I have found an issue with how MS SQL Server handles CROSS APPLY.
The database I'm working with has a pricing system with the following schema:
Service -> Price Model <- Price Component ('->' indicates a Foreign Key pointing to the table)
Some…
dcembree83
- 73
- 2
- 8
4
votes
3 answers
Using CTE instead of Cursor
I have the following table structure.
I just want to update SubId to all the rows where it is null and where the RawLineNumber is ascending by 1 and also the SeqNumber ascending by 1.
RawlineNumber Claimid SubId SeqNumber
1 6000 …
Ashley John
- 2,379
- 2
- 21
- 36