SQL Server行转列、不确定列的行转列
CREATE TABLE
(
StudentName NVARCHAR( 50 ) NOT NULL ,
StudentSubject NVARCHAR( 50 ) NOT NULL ,
StudentGrade DECIMAL ( 4 , 1 ) NOT NULL
)
INSERT INTO
INSERT INTO
INSERT INTO
INSERT INTO
INSERT INTO
INSERT INTO
SELECT * FROM
SELECT StudentName 学生名,
MAX ( CASE WHEN StudentSubject= '语文' THEN StudentGrade END ) 语文,
MAX ( CASE WHEN StudentSubject= '数学' THEN StudentGrade END ) 数学,
MAX ( CASE WHEN StudentSubject= '英语' THEN StudentGrade END ) 英语
FROM
GROUP BY StudentName
SELECT * FROM
DECLARE @SqlText NVARCHAR( 4000 ) = 'SELECT StudentName ''学生名'', '
SELECT @SqlText = @SqlText + ' MAX(CASE WHEN StudentSubject=''' + StudentSubject+ ''' THEN StudentGrade END) ''' + StudentSubject + ''',' FROM ( SELECT DISTINCT StudentSubject FROM
SELECT @SqlText = LEFT ( @SqlText , LEN ( @SqlText ) - 1 ) + ' FROM #TempSubjectResult GROUP BY StudentName'
PRINT @SqlText
EXEC ( @SqlText )
DECLARE @SqlSubject NVARCHAR( 4000 )
SELECT @SqlSubject = STUFF( ( SELECT ',' + '[' + StudentSubject+ ']' FROM ( SELECT DISTINCT StudentSubject FROM
DECLARE @SqlPIVOT NVARCHAR( 4000 ) = 'SELECT * FROM #TempSubjectResult PIVOT(MAX(StudentGrade) FOR StudentSubject IN(' + @SqlSubject + ')) T'
PRINT @SqlPIVOT
EXEC ( @SqlPIVOT )
DROP TABLE