locked
Having a little problem with Pivot table RRS feed

  • Question

  • User1216627406 posted

    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

    Monday, January 7, 2019 12:40 AM

All replies

  • User77042963 posted
    ....
    PIVOT
    ( SUM(AmountPaid) FOR [Year] IN (2016,2017,2018,2019,2020,2021) ) AS PVT
    Monday, January 7, 2019 8:33 PM
  • User1216627406 posted

    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

    Monday, January 7, 2019 8:38 PM