z**********8
发帖数: 2049 | 1 WITH PivotData AS
(
select CalendarYear, state, personid
from [dbo].[tblImportPerson])
select CalendarYear, [2008], [2009]
from pivotData
pivot ( sum(personid) for state IN ([2008],[2009])) as p；
--Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ')'.
谢谢！ | A*******n
发帖数: 625 | 2 为什么state是2008，2009？
sum(personid)也有点奇怪，要算多少个personid，用count吧
【在 z**********8 的大作中提到】
: WITH PivotData AS
: (
: select CalendarYear, state, personid
: from [dbo].[tblImportPerson])
: select CalendarYear, [2008], [2009]
: from pivotData
: pivot ( sum(personid) for state IN ([2008],[2009])) as p；
: --Msg 102, Level 15, State 1, Line 9
: Incorrect syntax near ')'.
: 谢谢！
| A*******n
发帖数: 625 | 3 try this:
select *
from (
select CalendarYear, state, personid
from [dbo].[tblImportPerson]
) as s
pivot
( count(personid)
for state in ([PA],[TX])
) as p | w****n
发帖数: 266 | 4 这个基本语法没错，查查数据类型
【在 z**********8 的大作中提到】
: WITH PivotData AS
: (
: select CalendarYear, state, personid
: from [dbo].[tblImportPerson])
: select CalendarYear, [2008], [2009]
: from pivotData
: pivot ( sum(personid) for state IN ([2008],[2009])) as p；
: --Msg 102, Level 15, State 1, Line 9
: Incorrect syntax near ')'.
: 谢谢！
| z**********8
发帖数: 2049 | 5 我所有的数据都ｖａｒｃｈａｒ, 也不行啊。
with pivotdata as
(
select state, year, personid
from [dbo].[Sheet2$]
)
select state, [2008], [2009]
from pivotdata
pivot(count(personid) FOR year in ([2008], [2009])) as p;
【在 w****n 的大作中提到】
: 这个基本语法没错，查查数据类型
| A*******n
发帖数: 625 | 6 如果你的类型都是nvarchar，你用count（1）试试， 我家有2012， 晚上我用2012试试 |
|
