locked
pivot issues can what is wrong with this code please RRS feed

  • 問題

  • select * From(
    Select
    Emp.empid                                                    AS 'Employee Id',
                 Emp.Firstname+' '+Emp.lastname     As 'Employee Full Name',
       Mng.Firstname+' '+Mng.lastname     As 'Manager Full Name',
      Convert(Nvarchar(20),year( So.orderdate))+'- 0'+Convert(Nvarchar(20),Month(so.orderdate))  As 'Order Date',
       Sum(sod.qty*sod.unitprice)                 As  'Order Value'
    From Hr.Employees AS Emp
    inner join
    Hr.Employees             AS Mng    On Emp.mgrid=Mng.empid
    inner join
    Sales.Orders                As So          On Mng.empid=So.empid
    inner join     
    Sales.OrderDetails    As Sod       On So.orderid=Sod.orderid
    where year(so.orderdate)='2007' and month(so.orderdate)<='6'
    Group By Emp.empid, Emp.Firstname+' '+Emp.lastname,Mng.Firstname+' '+Mng.lastname,   
      Convert(Nvarchar(20),year( So.orderdate))+'- 0'+Convert(Nvarchar(20),Month(so.orderdate))
    --Order by 'order date'
    ) As managerOrderValue
    pivot (
    sum(ordervalue)  for orderdate in (2007-01,2007-02,2007-03,2007-04,2007-05,2007-06)
    )as pvt
      ;
    Go

    this is the error message

     Msg 102, Level 15, State 1, Line 21
    Incorrect syntax near '2007'.

    • 已編輯 ulianna 2020年8月8日 下午 07:38
    2020年8月8日 下午 07:36

所有回覆

  • To solve the syntax error, try

       … for orderdate in ('2007-01', '2007-02', '2007-03', …

    or

       … for orderdate in ('2007-01-01', '2007-02-01', '2007-03-01', …

    But if results are not good, then describe the problem accordingly.


    • 已編輯 Viorel_MVP 2020年8月8日 下午 08:08
    2020年8月8日 下午 08:06
  • Error:

    Msg 102, Level 15, State 1, Line 21
    Incorrect syntax near '2007'.

    Problem Clause : where year(so.orderdate)='2007' 

    Problem : Your trying to compare int with varchar in this clause

    Left Side:

    Your orderdate I assume is a date variable and you are storing the dates

    When you select year(so.orderdate) to type returned is Int.

    Try these command to confirm

    DECLARE @a date
    SELECT @a = getdate()
    SELECT YEAR(@a)
    SELECT SQL_VARIANT_PROPERTY((SELECT YEAR(@a)),'BaseType') BaseType

    Right Side:

    However on the right side is taking the varchar type

    Try these command to verify 

    SELECT SQL_VARIANT_PROPERTY('2020','BaseType') BaseType

    Resolution:

    Do and explicit conversion to '2007' as INT

    Example

    where year(so.orderdate) =(CAST ('2007' as int))

    Let me know if it works and do mark as answered or up vote this reply if it helps

    2020年8月8日 下午 08:13
  • ok thank you very much ,let me try it

    2020年8月8日 下午 08:16
  • ok thank you
    2020年8月8日 下午 08:17
  • ok I tried it and it didn't work the thing is I m actually trying to pivot this using a derived table

    select Emp.empid                                                    AS 'Employee Id',
                 Emp.Firstname+' '+Emp.lastname     As 'Employee Full Name',
       Mng.Firstname+' '+Mng.lastname     As 'Manager Full Name',
      Convert(Nvarchar(20),year( So.orderdate))+'- 0'+Convert(Nvarchar(20),Month(so.orderdate))  As 'Order Date',
       Sum(sod.qty*sod.unitprice)                 As  'Order Value'
    From Hr.Employees AS Emp
    inner join
    Hr.Employees             AS Mng    On Emp.mgrid=Mng.empid
    inner join
    Sales.Orders                As So          On Mng.empid=So.empid
    inner join     
    Sales.OrderDetails    As Sod       On So.orderid=Sod.orderid
    where year(so.orderdate)='2007' and month(so.orderdate)<='6'
    Group By Emp.empid, Emp.Firstname+' '+Emp.lastname,Mng.Firstname+' '+Mng.lastname,   
      Convert(Nvarchar(20),year( So.orderdate))+'- 0'+Convert(Nvarchar(20),Month(so.orderdate))
    Order by 'order date'
      ;
    Go


    2020年8月8日 下午 08:25
  • Error:

    Msg 102, Level 15, State 1, Line 21
    Incorrect syntax near '2007'.

    Problem Clause : where year(so.orderdate)='2007' 

    Problem : Your trying to compare int with varchar in this clause

    Left Side:

    Your orderdate I assume is a date variable and you are storing the dates

    When you select year(so.orderdate) to type returned is Int.

    Try these command to confirm

    DECLARE @a date
    SELECT @a = getdate()
    SELECT YEAR(@a)
    SELECT SQL_VARIANT_PROPERTY((SELECT YEAR(@a)),'BaseType') BaseType

    Right Side:

    However on the right side is taking the varchar type

    Try these command to verify 

    SELECT SQL_VARIANT_PROPERTY('2020','BaseType') BaseType

    Resolution:

    Do and explicit conversion to '2007' as INT

    Example

    where year(so.orderdate) =(CAST ('2007' as int))

    Let me know if it works and do mark as answered or up vote this reply if it helps

    ok I tried it and it didn't work the thing is I m actually trying to pivot this using a derived table

    select Emp.empid                                                    AS 'Employee Id',
                 Emp.Firstname+' '+Emp.lastname     As 'Employee Full Name',
       Mng.Firstname+' '+Mng.lastname     As 'Manager Full Name',
      Convert(Nvarchar(20),year( So.orderdate))+'- 0'+Convert(Nvarchar(20),Month(so.orderdate))  As 'Order Date',
       Sum(sod.qty*sod.unitprice)                 As  'Order Value'
    From Hr.Employees AS Emp
    inner join
    Hr.Employees             AS Mng    On Emp.mgrid=Mng.empid
    inner join
    Sales.Orders                As So          On Mng.empid=So.empid
    inner join     
    Sales.OrderDetails    As Sod       On So.orderid=Sod.orderid
    where year(so.orderdate)='2007' and month(so.orderdate)<='6'
    Group By Emp.empid, Emp.Firstname+' '+Emp.lastname,Mng.Firstname+' '+Mng.lastname,   
      Convert(Nvarchar(20),year( So.orderdate))+'- 0'+Convert(Nvarchar(20),Month(so.orderdate))
    Order by 'order date'
      ;
    Go


    2020年8月8日 下午 09:19
  • Hi ulianna,

    Please use the [ ]:

    pivot (
    sum(ordervalue)  for orderdate in ([2007-01],[2007-02],[2007-03],[2007-04],[2007-05],[2007-06])
    )as pvt

    Best regards,
    Cris


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    2020年8月10日 上午 06:15
  • Hi ulianna,

    Is there any update on this case? Was your issue resolved? 

    If you have resolved your issue, please mark the useful reply as answer. This can be beneficial to other community members reading the thread.
    In addition, if you have another questions, please feel free to ask.

    Best regards,
    Cris


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    2020年8月11日 上午 12:50