Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2010
    Posts
    9

    Unanswered: Error in Pivot query

    I have the following query

    Code:
     DECLARE @query NVARCHAR(4000)
    DECLARE @Days NVARCHAR(4000)
    SELECT  @Days = STUFF((SELECT DISTINCT
                            '],[' + ltrim(str(datepart(dw,visitdate)))
                            FROM    mktPlanHospitals
                            ORDER BY '],['  + ltrim(str(datepart(dw,visitdate)))
                            FOR XML PATH('')                        ), 1, 2, '') + ']'
    
    SET @query =
    'SELECT * FROM
    (
        SELECT datepart(dw,visitdate) as vdate,DoctorID
        FROM mktPlanHospitals
    )t
    PIVOT (SUM(DoctorID) FOR datepart(dw,visitdate)
    IN ('+@Days+')) AS pvt'
    
    EXECUTE (@query)
    It is giving me error

    Code:
    Incorrect syntax near '('.

  2. #2
    Join Date
    Aug 2011
    Posts
    12

    Answer

    Hi,

    I evaluated your query in Sql server 2008 management studio Enterprise edition,
    in pivot syntax after word "for" you wrote this expression "datepart(dw,ModifiedDate)" when I replace this expression with it's alias name that you calculated above line in your query,I mean "vdate" word.
    After that the error message gone.

    I hope this works for you.

  3. #3
    Join Date
    Jun 2010
    Posts
    9
    Thanks this solves my issue

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •