1

This is my query.

Declare
    @Pivv NVarchar(Max),
    @Query NVarchar(Max),
    @Pivv1 NVarchar(Max),
    @Columns NVarchar(Max)

Select
    @Pivv = Coalesce(@Pivv + ',' , '') + 
            Quotename(MatchType + '_' + '_Name'),
    @Pivv1 = Coalesce(@Pivv1 + ',' , '') + 
             Quotename('HighestRunsAndWicketsIn' + MatchType),
    @Columns = Isnull(@Columns + ',', '') +
               'Max(' + (MatchType + '_' + '_Name') + ') As ' + (MatchType + '_' + '_Name') +
               ',' +
               'Max(' + ('HighestRunsAndWicketsIn' + MatchType) + ') As ' + ('HighestRunsAndWicketsIn' + MatchType)
From
    PlayersCareerBatting t Join PlayersBioDataNew a
On
    t.Id=a.Id
Group By
    MatchType

Set @Query=
        'Select
            ''Batting'',
            ' + @Columns + '
        From (
                Select
                    MatchType + ''_'' + ''_Name'' As Type1,
                    FirstName + '' '' + MiddleName + '' '' + LastName As Runs1,
                    ''HighestRunsAndWicketsIn'' + MatchType As Type,
                    RunsScored As Runs
                From ( 
                        Select 
                            FirstName, 
                            MiddleName,
                            LastName, 
                            MatchType, 
                            RunsScored
                        From (
                                Select 
                                    s.Id, 
                                    FirstName, 
                                    MiddleName,
                                    LastName, 
                                    MatchType, 
                                    RunsScored, 
                                    Row_Number() Over(Partition By MatchType Order By RunsScored Desc) rn
                                From 
                                    PlayersCareerBatting s Join PlayersBioDataNew a 
                                On s.Id=a.Id
                             ) x
                        Where rn=1
                     ) x2
             ) x3
        Pivot 
            (
                Max(Runs1) For Type1 In (' + @Pivv + ')
            ) x4
        Pivot
            (
                Max(Runs) For Type In (' + @Pivv1 + ')
            ) x5

        Union All

        Select
            ''Bowling'',
            ' + @Columns + '
        From (
                Select
                    MatchType + ''_'' + ''_Name'' As Type1,
                    FirstName + '' '' + MiddleName + '' '' + LastName As Wickets1,
                    ''HighestRunsAndWicketsIn'' + MatchType As Type,
                    Wickets As Wickets
                From ( 
                        Select 
                            FirstName, 
                            MiddleName,
                            LastName,
                            MatchType, 
                            Wickets
                        From (
                                Select 
                                    s.Id, 
                                    FirstName, 
                                    MiddleName,
                                    LastName, 
                                    MatchType, 
                                    Wickets, 
                                    Row_Number() Over(Partition By MatchType Order By Wickets Desc) rn
                                From 
                                    PlayersBowlingCareer s Join PlayersBioDataNew a 
                                On s.Id=a.Id
                             ) x6
                        Where rn=1
                     ) x7
             ) x8 
        Pivot 
            (
                Max(Wickets1) For Type1 In (' + @Pivv + ')
            ) x9
        Pivot
            (
                Max(Wickets) For Type In (' + @Pivv1 + ')
            ) x10'

Exec(@Query)

Query contains 3 tables. Playersbiodatanew (5000 rows), players?careerbatting (20000 rows), playersbowlingcareer (20000 rows). And, i created pivot in that query. Now i want to know, is that query more suitable for the execution plan mentioned below

execution plan

execution plan

execution plan

Link for execution plan, https://www.brentozar.com/pastetheplan/?id=SyJPRDs9M

Sample Data

For Table PlayersBioDataNew

Id | Firstname | middlename | lastname
----------------------------------------
1  | Sachin    | Ramesh     | Tendulkar
----------------------------------------
2  | Suresh    | Kumar      | Raina
----------------------------------------

For Table PlayersCareerBatting

CareerId | Id | Matchtype | runsscored
---------------------------------------
1        | 1  | Test      | 15921
----------------------------------------
2        | 1  | ODI       | 18426
----------------------------------------
3        | 1  | T20I      | 10
----------------------------------------
4        | 1  | IPL       | 2334
----------------------------------------
5        | 2  | Test      | 768
----------------------------------------
6        | 2  | ODI       | 5568
----------------------------------------
7        | 2  | T20I      | 1498
----------------------------------------
8        | 2  | IPL       | 4540
----------------------------------------

For Table PlayersBowlingCareer

CareerId | Id | Matchtype | wickets
---------------------------------------
1        | 1  | Test      | 46
----------------------------------------
2        | 1  | ODI       | 154
----------------------------------------
3        | 1  | T20I      | 1
----------------------------------------
4        | 1  | IPL       | 0
----------------------------------------
5        | 2  | Test      | 13
----------------------------------------
6        | 2  | ODI       | 36
----------------------------------------
7        | 2  | T20I      | 13
----------------------------------------
8        | 2  | IPL       | 25
----------------------------------------

Expected Output

(No Column Name) | IPL__Name | HighestRunsAndWicketsInIPL | Test__Name | HighestRunsAndWicketsInTest | T20I__Name | HighestRunsAndWicketsInT20I | ODI__Name | HighestRunsAndWicketsInODI
----------------------------------------------------------------------------
Batting | Suresh Kumar Raina | 4540 | Sachin Ramesh Tendulkar | 15921 | Suresh Kumar Raina | 1498 | Sachin Ramesh Tendulkar | 18426
----------------------------------------------------------------------------
Bowling | Suresh Kumar Raina | 25 | Sachin Ramesh Tendulkar | 46 | Suresh Kumar Raina | 13 | Sachin Ramesh Tendulkar | 154
----------------------------------------------------------------------------

I'm getting the output who got maximum runsscored and wickets for each matchtype.

2

how many rows in final output ?

From your script one cannot make out which column belong to which table.

you should prefix table alias along with column

Alternate Solution,

Since you are using `PlayersBioDataNew` in 3 places.Why not insert them in temp table and use temp table instead

create table #tmp (id int, rn int,matchtype int, wickets int)

insert into #tmp
select id, Row_Number() Over(Partition By MatchType Order By Wickets Desc) rn
,MatchType,wickets
 from  dbo.PlayersBioDataNew
 -- where apply other filter
 -- may be apply rn=1 here itself if it help in all 3 places

 --so depeding upon final script we can create index on #tmp table

 Declare
    @Pivv NVarchar(Max),
    @Query NVarchar(Max),
    @Pivv1 NVarchar(Max),
    @Columns NVarchar(Max)

Select
    @Pivv = Coalesce(@Pivv + ',' , '') + 
            Quotename(MatchType + '_' + '_Name'),
    @Pivv1 = Coalesce(@Pivv1 + ',' , '') + 
             Quotename('HighestRunsAndWicketsIn' + MatchType),
    @Columns = Isnull(@Columns + ',', '') +
               'Max(' + (MatchType + '_' + '_Name') + ') As ' + (MatchType + '_' + '_Name') +
               ',' +
               'Max(' + ('HighestRunsAndWicketsIn' + MatchType) + ') As ' + ('HighestRunsAndWicketsIn' + MatchType)
From
    PlayersCareerBatting t Join #tmp a
On
    t.Id=a.Id
Group By
    MatchType

Set @Query=
        'Select
            ''Batting'',
            ' + @Columns + '
        From (
                Select
                    MatchType + ''_'' + ''_Name'' As Type1,
                    FirstName + '' '' + MiddleName + '' '' + LastName As Runs1,
                    ''HighestRunsAndWicketsIn'' + MatchType As Type,
                    RunsScored As Runs
                From ( 

                                Select 
                                    FirstName, 
                                    MiddleName,
                                    LastName, 
                                    MatchType, 
                                    RunsScored
                                From 
                                    PlayersCareerBatting s Join #tmp a 
                                On s.Id=a.Id
                                Where rn=1


                     ) x2
             ) x3
        Pivot 
            (
                Max(Runs1) For Type1 In (' + @Pivv + ')
            ) x4
        Pivot
            (
                Max(Runs) For Type In (' + @Pivv1 + ')
            ) x5

        Union All

        Select
            ''Bowling'',
            ' + @Columns + '
        From (
                Select
                    MatchType + ''_'' + ''_Name'' As Type1,
                    FirstName + '' '' + MiddleName + '' '' + LastName As Wickets1,
                    ''HighestRunsAndWicketsIn'' + MatchType As Type,
                    Wickets As Wickets
                From ( 

                                Select 
                                    FirstName, 
                                    MiddleName,
                                    LastName,
                                    MatchType, 
                                    Wickets
                                From 
                                    PlayersBowlingCareer s Join #tmp a 
                                On s.Id=a.Id
                                Where rn=1


                     ) x7
             ) x8 
        Pivot 
            (
                Max(Wickets1) For Type1 In (' + @Pivv + ')
            ) x9
        Pivot
            (
                Max(Wickets) For Type In (' + @Pivv1 + ')
            ) x10'

Exec(@Query)

use sp_executesql in any case

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.