SQL SERVER列转换行及UNPIVOT

--***************列传行***************************

--构建样例表及初始化数据
IF OBJECT_ID('TB')IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB
(
     姓名 NVARCHAR(10)
    ,语文 INT
    ,数学 INT
    ,物理 INT
)
INSERT INTO TB VALUES(N'张三',74,83,93)
INSERT INTO TB VALUES(N'李四',74,84,94)

SELECT * FROM TB WITH(NOLOCK)
GO

--SQL SERVER 2000静态SQL
SELECT 
    * 
FROM ( 
        SELECT 
            姓名 
           ,N'语文' AS 课程
           ,语文 AS 分数 
        FROM TB 
        UNION ALL

        SELECT 
            姓名 
           ,N'数学' AS 课程
           ,数学 AS 分数 
        FROM TB 
        UNION ALL

        SELECT 
            姓名 
           ,N'物理' AS 课程
           ,物理 AS 分数 
        FROM TB
     ) AS TT 
ORDER BY TT.姓名

--SQL SERVER 2000动态SQL
DECLARE @SQL6 nvarchar(MAX)
SELECT 
    @SQL6=ISNULL(@SQL6+' 
                    UNION ALL ','')+ N'SELECT 
                       姓名 
                      ,N'''+[Name]+ N''' 课程,'+[Name] +N' 分数 FROM TB ' 
                   FROM sys.columns 
                   WHERE 
                       [object_id]=OBJECT_ID('TB')  AND[Name]!=N'姓名'
SET @SQL6=N'SELECT 
                * 
            FROM ('+@SQL6+N') AS T 
            ORDER BY T.姓名'
PRINT @SQL6

EXEC(@SQL6) 

 

UNPIVOT--列名转换列值，即列转行

Table_Source

UNPIVOT

(

   value_column

   FOR pivot_column

   IN(列名列表)

)别名

----SQL SERVER 2005静态SQLSELECT 
    姓名,分数,课程
FROM TB 
UNPIVOT
(
     分数 FOR 课程 IN 
    (
         语文
        ,数学
        ,物理
    )
) AS T

 ----SQL SERVER 2005动态SQL
DECLARE @SQL7 NVARCHAR(MAX)
 SELECT @SQL7=ISNULL(@SQL7+',','')+[Name] FROM sys.columns WHERE 
                       [object_id]=OBJECT_ID('TB')  AND[Name]!=N'姓名'
SET @SQL7= N'SELECT 姓名,分数,课程 FROM TB UNPIVOT( 分数 FOR 课程 IN ('+@SQL7+N')) AS T ORDER BY 姓名'
PRINT @SQL7
EXEC SP_EXECUTESQL @SQL7

/*运行结果：
    姓名  课程  分数
    ---------- -----------
    张三  语文  74
    张三  数学  83
    张三  物理  93
    李四  语文  74
    李四  数学  84
    李四  物理  94
*/