SQLSERVER——PIVOT列转行

PIVOT概述：
PIVOT用于将列值旋转为列名（即行转列），并在必要时对最终输出中所需的任何其余列值执行聚合。

PIVOT的一般语法：
SELECT [新表字段1，2，3…] FROM [原表名]
AS [原表别名]
PIVOT( [聚合函数] ( [原表字段1] ) FOR [原表字段2] IN ( [原表2值1]，[原表字段2值2]… ) ) AS [新表别名]

语法解释：
1、PIVOT必须列举[原表字段2的值]，列举的值必须用中括号 [ ] 包含起来，就算是字符串类型也不需要单引号 ’ ’
2、PIVOT中列举的值将作为新表的字段名称
3、为什么会有聚合函数？此处并没有GROUP BY 呀！偷偷告诉你，GROUP BY 是隐藏的，除了语句中出现的两个 [原表字段]，其他[原表字段]将被GROUP BY，这样才使得上面的PIVOT结果出现多行
4、列举字段的这个组在原表中没有数据将以NULL值存在于PIVOT后的新表
5、PIVOT 和 UNPIVOT 关系运算符将表值表达式更改为pivot后的新表
原文链接：https://blog.csdn.net/miaomiaotiaopi/article/details/126128377

测试例子：
--按年月分组查出库记录
DECLARE @p_edate VARCHAR(10)
SELECT @p_edate = '2023-09-01'

IF EXISTS
(
    SELECT *
    FROM tempdb.dbo.sysobjects
    WHERE id = OBJECT_ID(N'tempdb.dbo.#tempa')
) --是否存在该临时表
    DROP TABLE #tempa --存在则删除

CREATE TABLE #tempa
(
    yyyymm VARCHAR(50),
    item_code VARCHAR(50),
    qty DECIMAL(16, 8)
)

IF EXISTS
(
    SELECT *
    FROM tempdb.dbo.sysobjects
    WHERE id = OBJECT_ID(N'tempdb.dbo.#tempb')
) --是否存在该临时表
    DROP TABLE #tempb --存在则删除

CREATE TABLE #tempb
(
    yyyymm VARCHAR(50)
)

--品号库存统计
INSERT INTO #tempa
(
    yyyymm,
    item_code,
    qty
)
SELECT (CASE
            WHEN t.mm > 0 THEN
                CAST(t.yyyy AS VARCHAR(4)) + '年' + CAST(t.mm AS VARCHAR(2)) + '月'
            ELSE
                CAST(t.yyyy AS VARCHAR(4)) + '年'
        END
       ) yyyymm,
       t.ITEM_CODE,
       t.chuku_qty
FROM
(
    SELECT YEAR(tl.TRANSACTION_DATE) yyyy,
           0 mm,
           i.ITEM_CODE,
           SUM(tl.INVENTORY_QTY) chuku_qty
    FROM dbo.ITEM AS i
        LEFT JOIN
        (
            SELECT tl2.ITEM_ID,
                   tl2.INVENTORY_QTY,
                   tl2.TRANSACTION_DATE
            FROM dbo.TRANSACTION_LINE AS tl2
            WHERE tl2.Owner_Org_ROid = 'C8BAE02C-0A60-4FD0-46A9-12140D245E5D'
                 AND
                  (
                      tl2.SOURCE_ID_RTK = 'SALES_ISSUE.SALES_ISSUE_D'
                      OR
                      (
                          tl2.SOURCE_ID_RTK = 'ISSUE_RECEIPT.ISSUE_RECEIPT_D'
                          AND tl2.INVENTORY_QTY > 0
                      )
                      OR
                      (
                          tl2.SOURCE_ID_RTK = 'TRANSACTION_DOC.TRANSACTION_DOC_D'
                          AND SUBSTRING(tl2.DOC_NO, 1, 4) IN ( '1109', '1113' )
                      )
                  )
                  AND tl2.STOCK_ACTION = -1
                  AND tl2.CATEGORY <> '1G'
                  AND YEAR(tl2.TRANSACTION_DATE) >= YEAR(@p_edate) - 5
                  AND YEAR(tl2.TRANSACTION_DATE) <> YEAR(@p_edate)
                  AND CONVERT(VARCHAR(10), tl2.TRANSACTION_DATE, 120) <= @p_edate
        ) tl
            ON tl.ITEM_ID = i.ITEM_BUSINESS_ID
    GROUP BY YEAR(tl.TRANSACTION_DATE),
             i.ITEM_CODE
    UNION ALL
    SELECT YEAR(tl.TRANSACTION_DATE) yyyy,
           MONTH(tl.TRANSACTION_DATE) mm,
           i.ITEM_CODE,
           SUM(tl.INVENTORY_QTY) chuku_qty
    FROM dbo.ITEM AS i
        LEFT JOIN
        (
            SELECT tl3.ITEM_ID,
                   tl3.INVENTORY_QTY,
                   tl3.TRANSACTION_DATE
            FROM dbo.TRANSACTION_LINE AS tl3
            WHERE tl3.Owner_Org_ROid = 'C8BAE02C-0A60-4FD0-46A9-12140D245E5D'
                  AND
                  (
                      tl3.SOURCE_ID_RTK = 'SALES_ISSUE.SALES_ISSUE_D'
                      OR
                      (
                          tl3.SOURCE_ID_RTK = 'ISSUE_RECEIPT.ISSUE_RECEIPT_D'
                          AND tl3.INVENTORY_QTY > 0
                      )
                      OR
                      (
                          tl3.SOURCE_ID_RTK = 'TRANSACTION_DOC.TRANSACTION_DOC_D'
                          AND SUBSTRING(tl3.DOC_NO, 1, 4) IN ( '1109', '1113' )
                      )
                  )
                  AND tl3.STOCK_ACTION = -1
                  AND tl3.CATEGORY <> '1G'
                  AND YEAR(tl3.TRANSACTION_DATE) = YEAR(@p_edate)
                  AND CONVERT(VARCHAR(10), tl3.TRANSACTION_DATE, 120) <= @p_edate
        ) tl
            ON tl.ITEM_ID = i.ITEM_BUSINESS_ID
    GROUP BY YEAR(tl.TRANSACTION_DATE),
             MONTH(tl.TRANSACTION_DATE),
             i.ITEM_CODE
) t
WHERE t.ITEM_CODE = '110060002'

--日历
INSERT INTO #tempb
(
    yyyymm
)
SELECT (CASE
            WHEN t2.yyyy < YEAR(@p_edate) THEN
                CAST(t2.yyyy AS VARCHAR(4)) + '年'
            ELSE
                CAST(t2.yyyy AS VARCHAR(4)) + '年' + CAST(t2.m AS VARCHAR(2)) + '月'
        END
       )
FROM
(
    SELECT t.yyyy,
           t.m
    FROM
    (
        SELECT ai.ACCOUNT_YEAR yyyy,
               MONTH(aid.START_DATE) m,
               CONVERT(VARCHAR(10), aid.START_DATE, 120) sdate,
               (CASE
                    WHEN YEAR(ai.ACCOUNT_YEAR) = YEAR(@p_edate)
                         AND MONTH(aid.START_DATE) < MONTH(@p_edate) THEN
                        CONVERT(VARCHAR(10), aid.CUTOFF_DATE, 120)
                    WHEN YEAR(ai.ACCOUNT_YEAR) = YEAR(@p_edate)
                         AND MONTH(aid.START_DATE) = MONTH(@p_edate) THEN
                        @p_edate
                    ELSE
                        CONVERT(VARCHAR(10), DATEADD(MONTH, -1, (DATEADD(YEAR, 1, aid.CUTOFF_DATE))), 120)
                END
               ) edate
        FROM dbo.ACCPERIOD_INFO AS ai
            INNER JOIN dbo.ACCPERIOD_INFO_D AS aid
                ON aid.ACCPERIOD_INFO_ID = ai.ACCPERIOD_INFO_ID
        WHERE ai.ApproveStatus = 'Y'
              AND ai.ACCOUNT_YEAR >= YEAR(@p_edate) - 5
              AND aid.START_DATE <= @p_edate
              AND
              (
                  (
                      YEAR(ai.ACCOUNT_YEAR) < YEAR(@p_edate)
                      AND MONTH(aid.START_DATE) = 1
                  )
                  OR
                  (
                      YEAR(ai.ACCOUNT_YEAR) = YEAR(@p_edate)
                      AND MONTH(aid.START_DATE) <= MONTH(@p_edate)
                  )
              )
    ) t
) t2


DECLARE @column NVARCHAR(MAX),
        @sql NVARCHAR(MAX)

SET @column = N''

SELECT @column += N'[' + CAST(t.yyyymm AS VARCHAR(50)) + N'],'
FROM #tempb AS t

SELECT @column = SUBSTRING(@column, 1, LEN(@column) - 1)

--例子SQL
--SELECT b.item_code,
--       [2018年],
--       [2019年],
--       [2020年],
--       [2021年],
--       [2022年],
--       [2023年1月],
--       [2023年2月],
--       [2023年3月],
--       [2023年4月],
--       [2023年5月],
--       [2023年6月],
--       [2023年7月]
--FROM #tempa AS a
--    PIVOT
--    (
--        SUM(qty)
--        FOR yyyymm IN ([2018年], [2019年], [2020年], [2021年], [2022年], [2023年1月], [2023年2月], [2023年3月], [2023年4月],
--                       [2023年5月], [2023年6月], [2023年7月]
--                      )
--    ) AS b

SELECT @sql
    = N'SELECT b.item_code, ' + @column + N' FROM #tempa AS a PIVOT(SUM(qty) for yyyymm in (' + @column + N')) as b '

PRINT @sql

EXEC (@sql)

 

品号出库统计：

 日历：

 

最后结果：