Revisions to How can we improve this PIVOT operation? ( SQL Server 2008 R2 )

added [query-performance] to 2412 questions - Shog9 (Id=1924)

Link

edited Jan 7, 2020 at 22:23

Community Bot

1

Bumped by Community user

occurred Jul 7, 2019 at 6:00

Bumped by Community user

occurred Mar 17, 2017 at 0:15

Incorporated comment; edited tags

Source Link

edited Jan 27, 2017 at 11:28

Paul White ♦

90.2k
30
423
663

XML plan: http://pastebin.com/raw/anLifzBC

sql-server sql-server-2008-r2 performance t-sql pivot

Tweeted twitter.com/#!/StackDBAs/status/639580348804796416

occurred Sep 3, 2015 at 23:26

deleted 13 characters in body

Source Link

edited Sep 3, 2015 at 21:28

Kin Shah

62.4k
6
122
240

    Selectselect top 100 * From
from (
    select codCliente
        ,Resposta
   Select  codCliente, Resposta  ,NrQuestao --,COUNT(1) as Qtde
        ,(
        ,(Select    select COUNT(1) as qtde 
 From           from tblRespostaINT T2 
 Where           where T2.codCliente = T0.codCliente 
   And             and T2.codNrQuestao between 9 
 and 29) as QResp
                From tblRespostaINTand T029
            Inner) as JoinQResp
 tblQuestao T1 On T0.codNrQuestaofrom =tblRespostaINT T1.codNrQuestaoT0
   
  inner join tblQuestao T1 on T0.codNrQuestao = T1.codNrQuestao
    Wherewhere T0.CodQuestionario = 1
        and T1.codNrQuestao between 9
   And T1.codNrQuestao between 9      and 29
                --and codCliente = 5156
                --Group by codCliente, Resposta,NrQuestao
    ) as tblx
Pivot(Sum(resposta) for NrQuestao in (
      as tblx     [I_1_P14_1]
      Pivot (Sum(resposta) for NrQuestao in ([I_1_P14_1] ,[I_2_P14_1]
            ,[I_3_P14_1]
            ,[I_4_P14_1]
            ,[I_5_P14_1],[I_6_P14_1],[I_7_P14_1],
            ,[I_6_P14_1]
            ,[I_7_P14_1]
            ,[I_9_P14_1]
         [I_9_P14_1]   ,[I_10_P14_1]
            ,[I_11_P14_1]
            ,[I_12_P14_1]
            ,[I_13_P14_1],[I_14_P14_1],[I_15_P14_1],
            ,[I_14_P14_1]
            ,[I_15_P14_1]
            ,[I_16_P14_1]
            [I_16_P14_1],[I_17_P14_1]
            ,[I_18_P14_1]
            ,[I_19_P14_1]
            ,[I_20_P14_1]
            ,[I_21_P14_1]
            ,[I_22_P14_1])
            )) tbl

Select select codCliente,
    ,Resposta
    ,NrQuestao --,COUNT(1) as Qtde
     ,(
        select COUNT(1) as qtde
    ,(Select COUNT(1) as qtde Fromfrom tblRespostaINT T2 
 Where       where T2.codCliente = T0.codCliente 
   And T2.codNrQuestao between 9 and 29) as QResp
  and T2.codNrQuestao between 9
               From tblRespostaINTand T029
             ) as QResp
from InnertblRespostaINT T0
inner Joinjoin tblQuestao T1 Onon T0.codNrQuestao = T1.codNrQuestao   
                Wherewhere T0.CodQuestionario = 1
                  Andand T1.codNrQuestao between 9 
 and 29
      and 29
            --and codCliente = 5156
                --Group by codCliente, Resposta,NrQuestao

Query PlanQuery Plan:

    Select top 100 * From (
                Select  codCliente, Resposta,NrQuestao--,COUNT(1) as Qtde
                ,(Select COUNT(1) as qtde From tblRespostaINT T2 Where T2.codCliente = T0.codCliente   And T2.codNrQuestao between 9 and 29) as QResp
                From tblRespostaINT T0
            Inner  Join tblQuestao T1 On T0.codNrQuestao = T1.codNrQuestao   
             Where T0.CodQuestionario = 1
              And T1.codNrQuestao between 9 and 29
              --and codCliente = 5156
            --Group by codCliente, Resposta,NrQuestao
      )
      as tblx
      Pivot (Sum(resposta) for NrQuestao in ([I_1_P14_1],[I_2_P14_1],[I_3_P14_1],[I_4_P14_1],[I_5_P14_1],[I_6_P14_1],[I_7_P14_1],
                                             [I_9_P14_1],[I_10_P14_1],[I_11_P14_1],[I_12_P14_1],[I_13_P14_1],[I_14_P14_1],[I_15_P14_1],
                                             [I_16_P14_1],[I_17_P14_1],[I_18_P14_1],[I_19_P14_1],[I_20_P14_1],[I_21_P14_1],[I_22_P14_1])
     ) tbl

Select  codCliente, Resposta,NrQuestao--,COUNT(1) as Qtde
                    ,(Select COUNT(1) as qtde From tblRespostaINT T2 Where T2.codCliente = T0.codCliente   And T2.codNrQuestao between 9 and 29) as QResp
                    From tblRespostaINT T0
                Inner  Join tblQuestao T1 On T0.codNrQuestao = T1.codNrQuestao   
                Where T0.CodQuestionario = 1
                  And T1.codNrQuestao between 9 and 29
                  --and codCliente = 5156
                --Group by codCliente, Resposta,NrQuestao

Query Plan:

select top 100 *
from (
    select codCliente
        ,Resposta
        ,NrQuestao --,COUNT(1) as Qtde
        ,(
            select COUNT(1) as qtde 
            from tblRespostaINT T2 
            where T2.codCliente = T0.codCliente 
                and T2.codNrQuestao between 9 
                    and 29
            ) as QResp
    from tblRespostaINT T0
    inner join tblQuestao T1 on T0.codNrQuestao = T1.codNrQuestao
    where T0.CodQuestionario = 1
        and T1.codNrQuestao between 9
            and 29
                --and codCliente = 5156
                --Group by codCliente, Resposta,NrQuestao
    ) as tblx
Pivot(Sum(resposta) for NrQuestao in (
            [I_1_P14_1]
            ,[I_2_P14_1]
            ,[I_3_P14_1]
            ,[I_4_P14_1]
            ,[I_5_P14_1]
            ,[I_6_P14_1]
            ,[I_7_P14_1]
            ,[I_9_P14_1]
            ,[I_10_P14_1]
            ,[I_11_P14_1]
            ,[I_12_P14_1]
            ,[I_13_P14_1]
            ,[I_14_P14_1]
            ,[I_15_P14_1]
            ,[I_16_P14_1]
            ,[I_17_P14_1]
            ,[I_18_P14_1]
            ,[I_19_P14_1]
            ,[I_20_P14_1]
            ,[I_21_P14_1]
            ,[I_22_P14_1]
            )) tbl

select codCliente
    ,Resposta
    ,NrQuestao --,COUNT(1) as Qtde
    ,(
        select COUNT(1) as qtde
        from tblRespostaINT T2 
        where T2.codCliente = T0.codCliente 
            and T2.codNrQuestao between 9
                and 29
        ) as QResp
from tblRespostaINT T0
inner join tblQuestao T1 on T0.codNrQuestao = T1.codNrQuestao
where T0.CodQuestionario = 1
    and T1.codNrQuestao between 9 
        and 29
            --and codCliente = 5156
            --Group by codCliente, Resposta,NrQuestao