Esta é a minha consulta.
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)
A consulta contém 3 tabelas. Playersbiodatanew (5000 linhas), players?careerbatting (20000 linhas), playersbowlingcareer (20000 linhas). E, eu criei pivô nessa consulta. Agora eu quero saber, essa consulta é mais adequada para o plano de execução mencionado abaixo
Link para plano de execução, https://www.brentozar.com/pastetheplan/?id=SyJPRDs9M
Dados de amostra
Para jogadores de mesaBioDataNew
Id | Firstname | middlename | lastname
----------------------------------------
1 | Sachin | Ramesh | Tendulkar
----------------------------------------
2 | Suresh | Kumar | Raina
----------------------------------------
Para Jogadores de MesaCarreiraBatting
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
----------------------------------------
Para Jogadores De MesaBowlingCarreira
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
----------------------------------------
Saída esperada
(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
----------------------------------------------------------------------------
Estou obtendo a saída de quem obteve o máximo de pontos e wickets para cada tipo de partida.