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.
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.
Member
255 Points
1159 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:
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
All-Star
123252 Points
10024 Posts
Moderator
Re: Having a little problem with Pivot table
Jan 07, 2019 08:33 PM|limno|LINK
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
Member
255 Points
1159 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