学习使用PIVOT

来源：互联网发布：dede游戏网站源码编辑：程序博客网时间：2024/06/04 19:23

假设有这样的一个需求:

有一个表中存储了某个部门的各个员工的每一年的各类薪金,
记录存储格式如:
员工姓名薪金数目薪金种类年份

现在要求根据员工的姓名进行查询,查询出某些员工各个年份的薪金总数,出来的结果要求
ColumnName: 年份员工1姓名员工2姓名 ...
ColumnValue: 年份薪金总数薪金总数

这个时候使用PIVOT来进行将行转换了列，操作就变得很方便，而由于PIVOT是静态的列，所以要使其起到动态的效果，只有用SQL拼接的方法实现，如

-- Test Data
IF OBJECT_ID('TEMPDB..#T_Money') IS NOT NULL
BEGIN
    DROP TABLE #T_Money
    PRINT 'Drop Complate.'
END
Create Table #T_Money
(
    [ID]            INT             NULL,
    [Name]          NVARCHAR(256)   NULL,
    [Money]         Money           NULL,
    [Type]          NVARCHAR(128)   NULL,
    [Year]          INT             NULL
);
INSERT INTO #T_Money VALUES(1,N'小斌',15000,N'月薪',2007);
INSERT INTO #T_Money VALUES(2,N'小斌',5000,N'奖金',2007);
INSERT INTO #T_Money VALUES(3,N'小斌',20000,N'月薪',2008);
INSERT INTO #T_Money VALUES(4,N'小斌',10000,N'奖金',2008);
INSERT INTO #T_Money VALUES(5,N'小珍',5000,N'月薪',2007);
INSERT INTO #T_Money VALUES(6,N'小珍',5000,N'奖金',2007);
INSERT INTO #T_Money VALUES(7,N'小珍',10000,N'月薪',2008);
INSERT INTO #T_Money VALUES(8,N'小珍',5000,N'奖金',2008);
-- Unite SQL
DECLARE @Names AS NVARCHAR(2048);
WITH T_NAMES AS
(
    SELECT DISTINCT
        [Name]
    FROM 
        #T_Money 
)
SELECT
    @Names = ISNULL(@Names + N',[', '[' ) 
                + CAST(Name AS NVARCHAR(256))
                + ']'
FROM
    T_NAMES
WHERE
    Name LIKE N'%斌%';
PRINT @Names;
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = N'
            SELECT 
                * 
            FROM 
            (
                SELECT 
                    [Name]
                    ,[Money]
                    ,[Year]
                FROM 
                    #T_Money 
            ) AS Tmp_PIV
            PIVOT
            (
                SUM(Money)
                FOR [Name] IN (' + @Names + ')
            ) AS Tmp_Money
            Order By 
                Year';
PRINT @SQL;
EXEC sp_executesql @SQL;

结果如：