Rotate a table

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?
Thanks

select *, (select dbo.vname(id,1) ) name1,(select dbo.vname(id,2)) name2,(select dbo.vname(id,3)) name3 from test1

Result correct:
id      name1      name2      name3
1      John      Bob      David
2      JJ      Dog      NULL
3      Jim      Tom      NULL

Table Test1:
id
1
2
3

Table Test:
id      name
1      John
1      Bob
1      David
2      JJ
2      Dog
3      Jim
3      Tom

CREATE FUNCTION [dbo].[vname]
(
@Id int
,@num int
)
RETURNS nvarchar(50)
AS
BEGIN
      
      DECLARE @vname nvarchar(50)

      ;with cte
      as
      (
      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

      RETURN @vname

END
SanPrgAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
calling that function is causing 3 calculations of row_number() of the whole table test,  PER ROW of table test1. I suspect you just need a query without a function:
SELECT
        t1.id
      , max(CASE WHEN n.rn = 1 THEN [name] END) AS name1
      , max(CASE WHEN n.rn = 2 THEN [name] END) AS name2
      , max(CASE WHEN n.rn = 3 THEN [name] END) AS name3
FROM Test1 AS t1
INNER JOIN (
            SELECT
                    id
                  , name
                  , row_number() over (partition BY id ORDER BY name) AS rn
            FROM Test
           ) AS n ON t1.id = n.id
GROUP BY
        t1.id
;

Open in new window

here there is just a single use of row_number()
see it at: http://sqlfiddle.com/#!6/2f47e/4
0
PortletPaulfreelancerCommented:
oh just noticed: something: I have ordered by [name] in the row_number()
you can change that to id if you prefer the results that way.
0
SanPrgAuthor Commented:
PortletPaul ,
Cool, I need one thing  count of names are not null.
For example
id      name1      name2      name3       cnt
1      John            Bob      David                 3
2      JJ                 Dog      NULL                   2
3      Jim             Tom      NULL                   2
0
PortletPaulfreelancerCommented:
SELECT
        t1.id
      , max(CASE WHEN n.rn = 1 THEN n.[name] END) AS name1
      , max(CASE WHEN n.rn = 2 THEN n.[name] END) AS name2
      , max(CASE WHEN n.rn = 3 THEN n.[name] END) AS name3
      , max(n.rn) AS cnt
FROM Test1 AS t1
INNER JOIN (
            SELECT
                    id
                  , name
                  , row_number() over (partition BY id ORDER BY name) AS rn
            FROM Test
           ) AS n ON t1.id = n.id
GROUP BY
        t1.id
;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SanPrgAuthor Commented:
Excellent, thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.