[MSSQL]PIVOT函数

PIVOT在帮助中这样描述滴:

可以使用 PIVOT 和 UNPIVOT 关系运算符将表值表达式更改为另一个表。PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式，并在必要时对最终输出中所需的任何其余列值执行聚合。UNPIVOT 与 PIVOT 执行相反的操作，将表值表达式的列转换为列值。

简单点理解就是行变列,UNPIVOT则是列变行,一个一个看

测试用的数据及表结构:

CREATE TABLE ShoppingCart(
    [Week] INT NOT NULL,
    [TotalPrice] DECIMAL DEFAULT(0) NOT NULL
)
INSERT INTO ShoppingCart([Week],[TotalPrice])
SELECT 1,10 UNION ALL
SELECT 2,20 UNION ALL
SELECT 3,30 UNION ALL
SELECT 4,40 UNION ALL
SELECT 5,50 UNION ALL
SELECT 6,60 UNION ALL
SELECT 7,70
SELECT * FROM ShoppingCart

输出结果:

来看下PIVOT怎么把行变列:

SELECT 'TotalPrice' AS [Week],[1],[2],[3],[4],[5],[6],[7]
FROM ShoppingCart PIVOT(SUM(TotalPrice) FOR [Week] IN([1],[2],[3],[4],[5],[6],[7])) AS T

输出结果

 

可以看出来,转换完成了,就这么个功能

再看一个UNPIVOT函数,与上述功能相反,把列转成行

我们直接使用WITH关键字把上述PIVOT查询当成源表,然后再使用UNPIVOT关键把它旋转回原来的模样,SQL脚本及结果如下:

WITH P AS (
    SELECT 'TotalPrice' AS [Week],[1],[2],[3],[4],[5],[6],[7]
    FROM ShoppingCart PIVOT(SUM(TotalPrice) FOR [Week] IN([1],[2],[3],[4],[5],[6],[7]))
    AS T
)
SELECT
    [WeekDay] AS [Week],
    [WeekPrice] AS [TotalPrice]
FROM P
UNPIVOT(
    [WeekPrice] FOR [WeekDay] IN([1],[2],[3],[4],[5],[6],[7])
)AS FOO

OK介绍完了,大概功能如此,但使用起来远不止如此,灵活运用威力无穷~

下边这个SQL语句,下边大段注释部分为前同事的作品,上半部分是我重写后的,可以看到,代码量减少了不少!多提宝贵意见!

ALTER PROCEDURE [dbo].[Report_ApplyStat]
 @TenantId int
AS
BEGIN
    /* 模板表 */
    DECLARE @TEMP TABLE([aType] INT)
    INSERT INTO @TEMP([aType])VALUES(1),(2);

    /* 查询条件 */
    WITH CONDITION AS(
        SELECT
            SC.PersonId,
            ISNULL(RE.PhaseId,0) AS [PhaseId],
            DATEDIFF(MONTH,SC.ApplyDate,GETDATE()) AS DIFF
        FROM SearchCV SC
            LEFT JOIN [REL_PersonJobStoreDB] RE ON SC.PersonId = RE.PersonId
        WHERE SC.TenantId = @TenantId),
    [ApplyCount] AS(SELECT 1 AS [TYPE],DATEDIFF(MONTH,ApplyDate,GETDATE()) AS DIFF,(PersonId) AS [ApplyCount] FROM SearchCV WHERE TenantId = @TenantId),
    [OfferCount] AS(SELECT 2 AS [TYPE],DIFF,(PersonId) AS [OfferCount] FROM CONDITION WHERE PhaseId = 4    ),
    [Result]    AS(
        SELECT [TYPE] AS [aType],[1],[2],[3],[4],[5],[6] FROM [ApplyCount] PIVOT(COUNT([ApplyCount]) FOR DIFF IN([1],[2],[3],[4],[5],[6]))AS T UNION ALL
        SELECT [TYPE] AS [aType],[1],[2],[3],[4],[5],[6] FROM [OfferCount] PIVOT(COUNT([OfferCount]) FOR DIFF IN([1],[2],[3],[4],[5],[6]))AS T )
    SELECT
        TP.[aType]                AS [aType],
        ISNULL(RE.[6],0)        AS [M1],
        ISNULL(RE.[5],0)        AS [M2],
        ISNULL(RE.[4],0)        AS [M3],
        ISNULL(RE.[3],0)        AS [M4],
        ISNULL(RE.[2],0)        AS [M5],
        ISNULL(RE.[1],0)        AS [M6]
    FROM @TEMP TP
        LEFT JOIN [Result] RE ON TP.[aType] = RE.[aType] Order by TP.aType

--定义表变量

--declare @TenantId  INT
--select @TenantId=100001
--DECLARE @temp1 table(
--      aType INT DEFAULT(0),
--      mon int,--取今天与入库时间的月份差,如SELECT DATEDIFF(MONTH,'2010-7-1',GETDATE()) = 2
--      total int
--      )
--DECLARE @temp2 table(
--      aType int NOT NULL default 0,
--      mon varchar(40),
--      total int)
--DECLARE @stat_date DATETIME
--DEClARE @Index int
--declare @s varchar(4000), @sql varchar(4000)
--SET @s = ''
--SET @Index=1
--SET  @stat_date = GETDATE()-150
----遍历出要统计的月份
--WHILE    month(@stat_date) <= month(GETDATE())
--BEGIN
--    Select  @s=@s+ 'M'+convert(varchar(20),@Index)+ ' = max(case when mon = '+QUOTENAME(left(CONVERT(varchar,@stat_date,102),7),'''')+ ' then total else 0 end),'
--    SET   @stat_date=DATEADD(MONTH, 1, @stat_date)
--    SET @Index=@Index+1

--END
--Select @s= SUBSTRING(@s,0,len(@s))
--print @s

--应聘总数
--insert into @temp1(aType,mon,total)
--select 1,DATEDIFF(MONTH,CreateDate,GETDATE()) AS mon,COUNT(*) total  from [REL_PersonJobStoreDB] where TenantId=@TenantId group by DATEDIFF(MONTH,CreateDate,GETDATE())
--IF(NOT EXISTS(SELECT 1 FROM @temp1))
--BEGIN
--    insert into @temp1(aType,mon,total)
--    SELECT 1,-1,0
--END
----匹配应聘标识号
--Update @temp1 set  aType=1

--已录用人数
--insert into @temp1(aType,mon,total)
--select 2,DATEDIFF(MONTH,CreateDate,GETDATE()),COUNT(*) total from [REL_PersonJobStoreDB] where TenantId=@TenantId and PhaseId = 4 group by DATEDIFF(MONTH,CreateDate,GETDATE())
--IF(NOT EXISTS(SELECT 1 FROM @temp1 WHERE aType=2))
--BEGIN
--    insert into @temp1(aType,mon,total)
--    SELECT 2,-1,0
--END

--SELECT * FROM @temp1
--匹配应聘标识号
--Update @temp2 set  aType=2
----合并表数据
--insert into @temp1 select * from @temp2

--select * from @temp1

--DECLARE @DATE DATETIME
--SET @DATE = GETDATE()
--SELECT GETDATE(),DATEADD(MONTH,-1,GETDATE()),DATEDIFF(MONTH,GETDATE(),DATEADD(MONTH,1,GETDATE()))
--select aType,mon,avg(total) total from @temp1
-- group by atype,mon

--select [aType],
--M1 = max(case when mon = 6 then total else 0 end),
--M2 = max(case when mon = 5 then total else 0 end),
--M3 = max(case when mon = 4 then total else 0 end),
--M4 = max(case when mon = 3 then total else 0 end),
--M5 = max(case when mon = 2 then total else 0 end),
--M6 = max(case when mon = 1 then total else 0 end)
--from
--(
-- select aType,mon,avg(total) total from @temp1
-- group by atype,mon
-- ) aa
-- group by [aType]



-- print @sql
--exec(@sql)

END

原代码即为32行以后,新代码为前32行,显然重构后减少了Bad Smell

 

猜测您可能对下边的文章感兴趣

SQL SERVER 2008 函数大全 - 字符串函数

SQL2008系统统计函数

[MSSQL]GROUPING SETS,ROLLUP,CUBE初体验

[MSSQL]ROW_NUMBER函数

[MSQL]RANK函数

[MSSQL]NTILE另类分页有么有?!

[MSQL]也说SQL中显示星期几函数

[MSSQL]COALESCE与ISNULL函数

[MSSQL]PIVOT函数

[MSSQL]FOR XML AUTO I

[MSSQL]FOR XML AUTO II

[MSSQL]TRY…CATCH…通用格式

如果您喜欢该博客请点击右下角推荐按钮,您的推荐是作者创作的动力!