Hi I have 2 tables Test1 and Test , and UDF.
I got what I need but the performance is bad, the query takes around 3 minutes for 4000 rows.
Do you have other solution to get same result but faster?
select *, (select dbo.vname(id,1) ) name1,(select dbo.vname(id,2)) name2,(select dbo.vname(id,3)) name3 from test1
id name1 name2 name3
1 John Bob David
2 JJ Dog NULL
3 Jim Tom NULL
CREATE FUNCTION [dbo].[vname]
DECLARE @vname nvarchar(50)
select id,name, ROW_NUMBER() over(partition by id order by id) as rn from test
SELECT top 1 @vname =name from cte where id=@id and rn=@num