Pivot Mistake

• antonio.perez

  SSC Enthusiast

  Points: 136

  More actions

  September 25, 2024 at 4:09 pm

  Go to Answer

  #4462351

  Hi all,

  I have this query where I make a pivot to extract the data segmented by Hours:

  select ‘Amount’ as Amount, [0] ,[1] ,[2] ,[2] ,[3] ,[4] ,[5] ,[6] ,[7] ,[8] ,[9] ,[10] ,[11] ,[12] ,[13] ,[14] ,[15] ,[16] ,[17] ,[18] ,[19] ,[20] ,[21] ,[22] ,[23]
  from (select
  sum(efeneg) ‘Amount’,left(hormsg,2) ‘Time’,left(hormsg,2) ‘Time’,left(hormsg,2) ‘Time’. 
  from [HYDRA_P.mssql.en.bs].[Hydra].sfb.io_datmeniom nolock
  where fecses =‘20230106’
  and codmer =‘001’ and tiptrn =‘ne’ and left(climer,3) <>‘175’.
  group by FecSes,left(hormsg,2)) as sourcetable
  PIVOT (SUM(AMOUNT) FOR Time in
  ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23])) as pivottable1
  
  

  his gives as result:

  

  but if I run only the ‘sourcetable’ of the previous query:

  select
  sum(efeneg) ‘Amount’,left(hormsg,2) ‘Time’. 
  from [HYDRA_P.mssql.en.bs].[Hydra].sfb.io_datmeniom nolock
  where fecses =‘20230106’
  and codmer =‘001’ and tiptrn =‘ne’ and left(climer,3) <>‘175’.
  group by FecSes,left(hormsg,2) order by 2

  the result is:

  

  as you can see the sentence with PIVOT does not show the result for hour 09.

  Can someone tell me what I am doing wrong?

• drew.allen

  SSC Guru

  Points: 76985

  More actions

  September 25, 2024 at 4:22 pm

  Answer

  #4462354

  I think you need to include the leading zero, since this is a string, not an integer.

  select ‘Amount’ as Amount, [0] ,[1] ,[2] ,[2] ,[3] ,[4] ,[5] ,[6] ,[7] ,[8] ,[9] ,[10] ,[11] ,[12] ,[13] ,[14] ,[15] ,[16] ,[17] ,[18] ,[19] ,[20] ,[21] ,[22] ,[23]
  from (select
  sum(efeneg) ‘Amount’,left(hormsg,2) ‘Time’,left(hormsg,2) ‘Time’,left(hormsg,2) ‘Time’. 
  from [HYDRA_P.mssql.en.bs].[Hydra].sfb.io_datmeniom nolock
  where fecses =‘20230106’
  and codmer =‘001’ and tiptrn =‘ne’ and left(climer,3) <>‘175’.
  group by FecSes,left(hormsg,2)) as sourcetable
  PIVOT (SUM(AMOUNT) FOR Time in
  ([00],[01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23])) as pivottable1
  

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• antonio.perez

  SSC Enthusiast

  Points: 136

  More actions

  September 26, 2024 at 7:49 am

  #4462549

  Thanks a lot Drew.

  It works perfectly

• Jeff Moden

  SSC Guru

  Points: 1004340

  More actions

  September 27, 2024 at 2:31 am

  #4462893

  antonio.perez wrote:

  Thanks a lot Drew.

  It works perfectly

  I think you may have a larger issue on your hands... It's a really bad idea to store temporal data as strings.

   

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)