行转列实例

	-- SQL2005
	/*
	DECLARE @CCODE VARCHAR(10)
	DECLARE @COLUMN VARCHAR(2000)
	SET @COLUMN = ''
	DECLARE CUR CURSOR
	FOR SELECT DISTINCT [CCODE] FROM CODE_TEMPLATE WHERE IGRADE = 1 ORDER BY CCODE
	OPEN CUR
	FETCH NEXT FROM CUR INTO @CCODE
	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF LEN(@COLUMN) > 0 SET @COLUMN = @COLUMN + ','
		SET @COLUMN = @COLUMN + '[' + @CCODE + ']'
		FETCH NEXT FROM CUR INTO @CCODE
	END
	CLOSE CUR
	DEALLOCATE CUR

	EXEC('
	SELECT CSECCODE,CASE WHEN IID = 2 THEN ''合计'' ELSE CSECNAME END CSECNAME,'+@COLUMN+'
	FROM (SELECT A.*,B.CSECNAME FROM #RowSet A LEFT JOIN SECTIONS B ON A.CSECCODE = B.CSECCODE) T
		PIVOT (
		SUM (FMONEY)
		FOR 
		[CCODE] IN ('+@COLUMN+')
	) AS P ORDER BY IID,CSECCODE')
	*/

	-- SQL2000
	
	SELECT DISTINCT [CCODE] INTO #TMPCODE FROM CODE_TEMPLATE WHERE IGRADE = 1
	ORDER BY CCODE

	DECLARE @ASQL VARCHAR(8000) 
	SET @ASQL = 'SELECT A.CSECCODE,CASE WHEN IID = 2 THEN ''合计'' ELSE CSECNAME END CSECNAME ' 
	SELECT @ASQL = @ASQL + ' , SUM(CASE CCODE WHEN ''' + CCODE + ''' THEN FMONEY ELSE NULL END) [' + CCODE + ']' 
	FROM #TMPCODE AS A 
	SET @ASQL = @ASQL + ' FROM #RowSet A LEFT JOIN SECTIONS B ON A.CSECCODE = B.CSECCODE 
	GROUP BY A.CSECCODE,IID,B.CSECNAME ORDER BY IID,A.CSECCODE' 
	--PRINT @ASQL	 
	EXEC(@ASQL)