none
PIVOT in query error RRS feed

  • Question

  • I'm using the following query and i am getting  "incorrect syntax near the keyword 'FOR'".

    I'm trying to use a pivot to show the last cost of the item by location. Can anyone help me figure out what am I doing wrong?

    Thank you!!

    SELECT 
    PV.ITEM_NO, [102],[104],[119]
    FROM
    (SELECT
    IM.ITEM_NO,
    IM.DESCR,
    IV.LOC_ID,
    IV.LST_COST
    FROM
    "company"."dbo"."IM_ITEM" AS IM
    LEFT OUTER JOIN
    "company"."dbo"."IM_INV" AS IV
    ON
    IM.ITEM_NO = IV.ITEM_NO) AS PV

    PIVOT
    (
    PV.ITEM_NO FOR PV.LOC_ID IN
    ([102],[104],[119])) AS PVT

    Friday, June 28, 2019 3:05 PM

Answers

All replies

  • Hi,

    You have to use an aggregate function before the FOR, something like this :

    PIVOT
    (
    MAX(PV.ITEM_NO) FOR PV.LOC_ID IN
    ([102],[104],[119])) AS PVT

    Please, refer to the following thread :

    https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    Friday, June 28, 2019 3:21 PM
    Answerer
  • That worked. Thanks a lot!!!!!
    Friday, June 28, 2019 3:39 PM
  • That worked. Thanks a lot!!!!!
    Hi chantipn,

    I am so glad to hear that 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.
    Thanks for your contribution.

    Best regards,
    Cathy Ji


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, July 1, 2019 7:40 AM