开发者

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)
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜