z**********8
发帖数: 2049 | 1 SELECT RecordSourceID, [1] AS JAN, [2] AS FEB, [3] AS MAR, [4] AS APR, [5]
AS MAY, [6] AS JUN, [7] AS JUL, [8] AS AUG, [9] AS SEP, [10] AS OCT, [11] AS
NOV, [12] AS DEC
FROM
(SELECT personid, Month(DateEntered), RecordSourceID
FROM dbo.tblPerson
WHERE RecordSourceID BETWEEN 152 AND 162 AND LoginPass IS NOT NULL AND year(
DateEntered)=2013) p
pivot
(
COUNT (personid)
FOR MONTH(DATEENTERED) IN
( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
)AS pvt
结果出不来啊。
Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near 'pivot'.
谢谢先！ | z**********8
发帖数: 2049 | 2 outcome is like this as below, (in ms sql server)
Recordsourceid, JAN, FEB, ..., DEC
152 50 30 10
153 20 33 5
154 .
.
.
.
162 | B*****g
发帖数: 34098 | 3 try give an alias for Month(DateEntered) in select then use it in for
AS
year(
【在 z**********8 的大作中提到】
: SELECT RecordSourceID, [1] AS JAN, [2] AS FEB, [3] AS MAR, [4] AS APR, [5]
: AS MAY, [6] AS JUN, [7] AS JUL, [8] AS AUG, [9] AS SEP, [10] AS OCT, [11] AS
: NOV, [12] AS DEC
: FROM
: (SELECT personid, Month(DateEntered), RecordSourceID
: FROM dbo.tblPerson
: WHERE RecordSourceID BETWEEN 152 AND 162 AND LoginPass IS NOT NULL AND year(
: DateEntered)=2013) p
: pivot
: (
| z**********8
发帖数: 2049 | 4 i tried it first.However it did not work either. so frustrated.
Month(dateentered) AS Months
thanks. | B*****g
发帖数: 34098 | 5 also: FOR MONTH(DATEENTERED) ==> FOR Months
【在 z**********8 的大作中提到】
: i tried it first.However it did not work either. so frustrated.
: Month(dateentered) AS Months
: thanks.
|
|
