今天又帮别人写了一段动态交叉表,几年没搞了,生疏了点,马上再记录一下

DECLARE @S VARCHAR(MAX)
SET @S = ''
SELECT  @S = @S + ',[' + TypeName + ']'
FROM    View_Analogy_DynamicQuery
GROUP BY TypeName
SET @S = STUFF(@S, 1, 1, '')

DECLARE @max VARCHAR(MAX)
SET @max = ''
SELECT  @max = @max + ',MAX([' + TypeName + ']) as [' + TypeName + ']'
FROM    View_Analogy_DynamicQuery
GROUP BY TypeName
SET @max = STUFF(@max, 1, 1, '')

EXEC('
select ResType,ReservoirVersionID,'+@max+' FROM (
select ResType,ReservoirVersionID,'+@S+' FROM View_Analogy_DynamicQuery 
PIVOT
( max(value)
for TypeName in ('+@S+')
) as PT ) x GROUP BY ResType,ReservoirVersionID
')

PIVOT 这个关键字前面貌似只能写from 某个表,如果from a join b on a.id = b.id 这样总是报错,无法深究到原因了,用一个视图把它封起来了