Stack Overflow
Stack Overflow

Questions tagged [cross-apply]

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

358 questions
-1
votes
2 answers

CROSS APPLY Creating Additional Records

I am trying to create a report that display potential duplicate records based on three criteria: the last 4 of an SSN, last name and DOB. I posted a question here on the issue and received an answer that I should be using a Cross Apply to unpivot…
MISNole
  • 992
  • 1
  • 22
  • 48
-1
votes
3 answers

How to convert multiple columns into a single row with row number?

I'm trying to figure out a way to convert [Column1],[Column2],[Column3],[Column4],[Column5] into a single column [Type]. I also want it to have row number. So an example of how I would want it to look like. ALso, what if I just want column with…
EkansDasnakE
  • 67
  • 1
  • 10
-1
votes
5 answers

SQL Select First column and for each row select unique ID and the last date

I have a problems this mornig , I have tried many solutions and nothing gave me the expected result. I have a table that looks like this : +----+----------+-------+ | ID | COL2 | DATE | +----+----------+-------+ | 1 | 1 | 2001 | | …
Clément Fayard
  • 135
  • 1
  • 2
  • 6
-1
votes
1 answer

Cross Apply using XML Path(comma delineated) - Remove Max Row if > 1

What I want is if it returns more than 1 then remove the MAX([year]) Row but return the rest. Meaning if Cross Applyselect returns 4 records return only 3. If 3 then return only 2. If 2 it returns only 1. I believe the best way is to change the…
sjackson5280
  • 43
  • 4
-2
votes
1 answer

How can I update a table using the following CROSS APPLY?

UPDATE ItemDim_DEV set ActiveFlag = 0, EndUTCDate = GETUTCDATE() from (select i.SKU, i.itemname, i.category, i.CategoryInternalId, i.itemtype, i.IsActive, i.assetaccount, i.InternalId, ni.sku, ni.itemname, ni.class_name, …
CreandoMentes
  • 3
  • 1
-2
votes
1 answer

Alternative for Cross Apply() in SQL

How can the below SQL query written using JOIN? I'm trying to convert SQL script to Trino/Presto and this does not support Cross Apply(). select i.*,k.* FROM dbo.[LN] i CROSS APPLY(select top 1 * from dbo.[LN] j where i.vnd_nbr =…
Vineet Nair
  • 1
  • 5
-2
votes
1 answer

Why CROSS APPLY and INNER JOIN returns different result

Why using cross apply return extra row. Isn't it should work similar to INNER JOIN? The result I'd expect is QuoteID controlNo etc MaxQuoteID COntrolNo 10101 1111 something15 10101 1111 Sample Data: -- create first…
Serdia
  • 4,242
  • 22
  • 86
  • 159
-2
votes
2 answers

Optimize query with multiple OUTER APPLY

I'm having query with multiple OUTER APPLY but all tables have primary keys on joined columns (so clustered indexes are used here) so I don't know how to optimize this query futher. Also it's impossible to use indexed view here because using ORDER…
Vadim Ovchinnikov
  • 13,327
  • 5
  • 62
  • 90
-2
votes
1 answer

How does the TOP, DESC and CROSS APPLY make this query faster?

Related to this question: In what order does execution on WHERE and ON clauses work? I was reading this page about…
dotnetN00b
  • 5,021
  • 13
  • 62
  • 95
-3
votes
2 answers

How to split more than one comma separated column as a separate row in SQL using CROSS APPLY

I have one table having following rows Name Phones Courses ABC 123, 456 HTML, Java XYZ 321, 654 PHP, CSS now I want to write a SELECT query to get these comma separated Phone and Courses as a separate row. By using this SELECT query I…
Muhammad Javed
  • 23
  • 1
  • 3
-3
votes
3 answers

SQL Server: Run separate CTE for each record without cursor or function

Given table A with a column LocationID and many records, Is it possible to fully run a CTE for each record, without using a cursor (while fetch loop) or function (via cross apply)? I can't run a CTE from the table A because the CTE will go deep…
igorjrr
  • 790
  • 1
  • 11
  • 22
-4
votes
1 answer

Function that receive table name as input variable to be used in CROSS APPLY

I need to create a Db function that returns a list of GUID, but from different tables. So I'm trying to create a db function, that get as input the name of the table, and return a list (table with only one field), but since I cannot use dynamic sql…
ff8mania
  • 1,553
  • 3
  • 19
  • 28
-5
votes
2 answers

SQL Server XML querying nested nodes

I need to get the values of Step0-to-Step1_Variance,Step1-to-Step2_Variance from the above XML: I tried with the query in the attached picture, but getting NULL value
Priya R
  • 1
  • 1
1 2 3
…
23
24