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
Failed to Convert Columns to Rows When Table has Different Data Types
I have a table like below:
Create table EmpDetails
(
ID int primary key,
DOB datetime not null,
Name nvarchar(100) not null,
Salary float not null,
Email nvarchar(50) not null,
IsActive bit not null
)
Insert into EmpDetails…
skjcyber
- 5,759
- 12
- 40
- 60
2
votes
2 answers
CROSS APPLY VS STUFF Function
I am searching for a GROUP_CONCAT equivalent function in SQL Server and I found this two options which are CROSS APPLY and STUFF. Because I want to concatenate the day of the sday field in the schedule table using join. Here is my schema for the…
user3093453
- 699
- 2
- 7
- 24
2
votes
3 answers
Using CROSS APPLY for more than one column
Day #3 with SQL Server.
I am trying to combine 2 columns of delimited data into one output from a Table Valued Function. Here is my data:
I would like the data to be processed and placed into a table in the following format:
I am currently trying…
Shrout1
- 2,497
- 4
- 42
- 65
2
votes
3 answers
Linq To SQL. How to prevent using Apply operator
I have a query:
var contactInfos = from person in persons
join tempDesiredCar in desiredCars on person.contact_id equals tempDesiredCar.groupEntity_id
into tempDesiredCars
…
Coder
- 41
- 4
2
votes
2 answers
T-SQL Using Cross-Apply with Delete Statement
I have the following tables:
RecordID
101
102
103
104
105
106
TableOne
101
102
103
104
TableTwo
TableThree
101
102
and I need to delete the RecordsID rows, that are not included in the other tables. Please, note that sometimes the one of the…
gotqn
- 42,737
- 46
- 157
- 243
2
votes
1 answer
CROSS APPLY Style versus Performance
Should I use a separate CROSS APPLY for each aliased expression or define multiple expressions in the same CROSS APPLY where possible?
I am refactoring many very complicated SQL queries (often four or five pages long with a dozen JOINs) that are…
Paul Chernoch
- 5,275
- 3
- 52
- 73
2
votes
2 answers
SQL Server: split record
I have a table like this:
account | check1 | check2
1 | 100]200]300 | 101]209]305
2 | 401]502 | 404]511
3 | 600 | 601
I want to separate the records into something like…
user1438730
- 31
- 4
1
vote
0 answers
What does the "B" mean in the linked answer, when the query still works however I name it?
Source: https://stackoverflow.com/a/56825537/3037607
Select A.[ID]
,C.*
From YourTable A
Cross Apply ( values (cast((Select A.* for XML RAW) as xml))) B(XMLData) -- This B here
Cross Apply (
Select Item =…
Mason
- 1,007
- 1
- 13
- 31
1
vote
1 answer
Flatten and group a set of data using PIVOT twice
I'm trying to flatten a set of data from a SQL query (using MS SQL Server) and I need to do it twice.
This is the example data I have.
This is the data I would like to show
I managed to get one height and one area for each building using PIVOT but…
Luca Sagoleo
- 63
- 4
1
vote
2 answers
Conditional CROSS APPLY in SQL Server
I have the following query which works perfectly fine:
Version #1
SELECT
t.ScheduleId,t.BaseDate,t.AfterDate,
fn.ScheduleDate AS NextBillingDate
INTO
#Distinct_BillableMemberAgreementItems
FROM
…
er.animesh
- 49
- 8
1
vote
1 answer
count values in json array per ID on multiple rows w/ OPENJSON
I have a table containing Id and JsonData columns (table has 100's of rows)
JsonData contains an array with various contact ID's for each…
im-devops
- 27
- 4
1
vote
1 answer
Retrieve the count of Sale records based on 2 week bracket from the recent verification date
CREATE TABLE [dbo].[Sale](
[ID] [int] NOT NULL,
[SaleDate] [date] NOT NULL,
[CustomerRef] [varchar](20) NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Verification](
[CustomerRef] [varchar](20) NOT NULL,
[VerificationDate] [date]…
variable
- 8,262
- 9
- 95
- 215
1
vote
1 answer
Display Two columns at the same time using Cross Apply
I have pretty much the same logic on the bottom but I am not able to put together to display two columns.
I am not sure how to combine two Cross Apply queries into one.
select
DateAdd(hour,hour_diff, ps) punch_start
from [dbo].[Stage]
cross…
Java
- 1,208
- 3
- 15
- 29
1
vote
2 answers
Transposing multiple related columns
While transposing single columns is pretty straight forward I need to transpose a large amount of data with 3 sets of , 10+ related columns needed to be transposed.
create table test
(month int,year int,po1 int,po2 int,ro1 int,ro2 int,mo1 int,mo2…
Bristle
- 77
- 6
1
vote
2 answers
How to use cross apply string split result to update a table in sql?
I am trying to split a column('categories') of a Table 'movies_titles' which has string separated data values in it.
e.g:
ID title categories
1 Movie A Comedy, Drama, Romance
2 Movie B Animation
3 Movie C Documentary, Life changing
I…
Sarah
- 29
- 1
- 1
- 4