DECLARE @s VARCHAR(MAX) SELECT @s=ISNULL(@s+',','')+QUOTENAME(ProjectName) FROM Tasks GROUP BY ProjectName EXEC (' SELECT PersonName AS 姓名,'+@s+' FROM ( SELECT PersonName,ProjectName,TaskContent FROM Tasks ) a PIVOT(MAX(TaskContent) FOR ProjectName IN('+@s+'))b ')
--2000的:DECLARE @s VARCHAR(8000) SELECT @s=ISNULL(@s,'')+',MAX(CASE ProjectName WHEN '''+ProjectName+''' THEN TaskContent ELSE 0 END)['+ProjectName+']' FROM Tasks GROUP BY ProjectName EXEC ('SELECT PersonName AS 姓名'+@s+'FROM Tasks GROUP BY PersonName')
MODIFY,小改一下DECLARE @s VARCHAR(8000) SELECT @s=ISNULL(@s,'')+',MAX(CASE ProjectName WHEN '''+ProjectName+''' THEN TaskContent ELSE 0 END)['+ProjectName+']' FROM Tasks GROUP BY ProjectName EXEC ('SELECT PersonName AS 姓名'+@s+' FROM Tasks GROUP BY PersonName')
SELECT @s=ISNULL(@s+',','')+QUOTENAME(ProjectName)
FROM Tasks
GROUP BY ProjectName
EXEC ('
SELECT PersonName AS 姓名,'+@s+'
FROM (
SELECT PersonName,ProjectName,TaskContent
FROM Tasks
) a
PIVOT(MAX(TaskContent) FOR ProjectName IN('+@s+'))b
')
消息 156,级别 15,状态 1,第 3 行
关键字 'FROM' 附近有语法错误。
消息 102,级别 15,状态 1,第 6 行
'a' 附近有语法错误。
SELECT @s=ISNULL(@s,'')+',MAX(CASE ProjectName WHEN '''+ProjectName+''' THEN TaskContent ELSE 0 END)['+ProjectName+']'
FROM Tasks
GROUP BY ProjectName
EXEC ('SELECT PersonName AS 姓名'+@s+'FROM Tasks GROUP BY PersonName')
SELECT @s=ISNULL(@s,'')+',MAX(CASE ProjectName WHEN '''+ProjectName+''' THEN TaskContent ELSE 0 END)['+ProjectName+']'
FROM Tasks
GROUP BY ProjectName
EXEC ('SELECT PersonName AS 姓名'+@s+' FROM Tasks GROUP BY PersonName')