Error in Pivot query

I have the following query

 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 DoctorID,datepart(dw,v开发者_JAVA技巧isitdate),DoctorID
    FROM mktPlanHospitals
)t
PIVOT (SUM(DoctorID) FOR datepart(dw,visitdate)
IN ('+@Days+')) AS pvt'

EXECUTE (@query)

It is giving me error

Incorrect syntax near '('.

---

You can't order by a field not specified in your SELECT, if you use the DISTINCT keyword. One distinct value could have several values to order it by, thus there could be no correct order.

Blog entry about the error.

---

Here is the working query that solves the problem

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 DoctorID,datepart(dw,VisitDate) as vdate
    FROM mktPlanHospitals
)t

PIVOT (SUM(DoctorID) FOR vdate
IN ('+@Days+')) AS pvt'

EXECUTE (@query)