Having a little problem with Pivot tableRSS

• • Reply
  simflex

  Member

  224 Points

  1055 Posts

  Having a little problem with Pivot table

  Jan 07, 2019 12:40 AM|simflex|LINK

  I am trying to perform pivot on registration_date which has a data type of datetime.

  Here is my query:

  SELECT *
  FROM ( 
  SELECT t.TransactionID, t.Amount, t.AmountPaid, t.AmountOwed, t.EventYear, t.Reg_Fee, p.Description, 
  m.FirstName + ' ' + m.LastName AS Members, m.EmailAddress, m.Phone, m.Address, m.gender,
  m.registration_date As [Year], m.enelope_number
  FROM dbo.Transactions AS t INNER JOIN dbo.Members AS m ON t.MemberID = m.MemberID 
                             INNER JOIN dbo.PaymentTypes AS p ON t.TypeID = p.PaymentTypeID
  ) Datatable 
  PIVOT ( 
   SUM(AmountPaid)
  FOR [Year] IN (Year)
  ) AS PVT

  Registration_date is in a range of 2016 to 2019 but the user has the option of adding 2020 or more.

  So far, I am getting the following error message:

  Msg 8114, Level 16, State 1, Line 18

  Error converting data type nvarchar to datetime.

  Msg 473, Level 16, State 1, Line 18

  The incorrect value "Year" is supplied in the PIVOT operator.

  Any ides how to resolve this?

  Thanks in advance

• • Reply
  limno

  All-Star

  122659 Points

  9844 Posts

  Moderator

  Re: Having a little problem with Pivot table

  Jan 07, 2019 08:33 PM|limno|LINK

  ....
  PIVOT ( 
   SUM(AmountPaid)
  FOR [Year] IN (2016,2017,2018,2019,2020,2021)
  ) AS PVT

  Jingyang Li
  Format your SQL query with instant sql formatter:
  http://www.dpriver.com/pp/sqlformat.htm
• • Reply
  simflex

  Member

  224 Points

  1055 Posts

  Re: Having a little problem with Pivot table

  Jan 07, 2019 08:38 PM|simflex|LINK

  Thanks @limno,

  I came here to delete the question but unfortunately, there is no delete feature on this forum.

  I recognized there is no way of doing what  I am trying to accomplish which is rather than manually add Years, it would have been nice to add the years on the back end and have it display the year number.

  However, I ended up doing it just the way you did it.

  Thanks again