Thursday, June 30, 2016

How to use Cross Apply in SQL Server

Here I will show how to use Cross Apply in SQL Server

Method 1:
To use this method we will use the following posts

1.  Function that Splits string in SQL Server / Table Valued Function
2. Create Temp Table in Sql Server or Create Temp Table Variable in Sql Server
3. Difference between #Table and @Table in sql server

OUT PUT:





Create a Temp Table as shown below.


 declare @TmpTbl as Table
(
       ID int identity(1,1),
       Names nvarchar(50)
)
insert into @TmpTbl (Names)values('Ravi')
insert into @TmpTbl (Names)values('Dinesh, Ramesh, Shiva')
insert into @TmpTbl (Names)values('Ramesh')
insert into @TmpTbl (Names)values('Vara')

select * from @TmpTbl

Table looks as shown below.

ID
Names
1
Ravi
2
Dinesh, Satish, Shiva
3
Ramesh
4
Vara

Here is the query for Cross Apply with the same table.

select a.ID,t.Data from @TmpTbl a cross apply dbo.SplitString(a.Names,',') t


No comments:

Post a Comment