4 个连接表 SQL Server 的 Pivot 和 Unpivot

本文介绍了4 个连接表 SQL Server 的 Pivot 和 Unpivot的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我使用的是 SQL Server 2012，我想创建一个包含 4 个连接表的数据透视表.

I'm using SQL Server 2012, and I want to create a pivot table with 4 joined tables.

这是我的查询:

SELECT
    a.Itemno, a.Qty as PlanMilling,
    ISNULL(b.MinimStock, 0) as MinStock,
    CAST(a.ScheduleDate as Date) AS Schedule,
    ISNULL(SUM(c.Qty), 0) as QtyBuilding,
    ISNULL(d.RunQty, 0) as QtyStock,
    d.itemcode,
    ISNULL((a.Qty + d.RunQty) - SUM(c.Qty), 0) as Balance
FROM
    Schedule a
LEFT OUTER JOIN
    Item b ON a.ItemNo = b.ItemNo
LEFT OUTER JOIN
    ShopOrderList c on a.ItemNo = c.ItemNo and a.ScheduleDate = c.ScheduleDate
LEFT OUTER JOIN
    (SELECT
         RunQty, ItemCode
     FROM
         ICMutTran a
     INNER JOIN
         (SELECT
              itemcode as ItemNo, max(id) as maxid
          FROM
              ICMutTran
          GROUP BY
              itemcode) AS b ON a.id = b.maxid) d ON (a.ItemNo = d.ItemCode)
GROUP BY
    a.ItemNo, a.Qty, b.MinimStock, a.ScheduleDate, d.RunQty, d.itemcode

结果:

ItemNo   |   PlanMilling   |   MinStock   |   Schedule   |   QtyBuilding   |   QtyStock   |   ItemCode   |
----------------------------------------------------------------------------------------------------------
123      |   1000          | 100          | 2016-02-04   | 200             | 1500         | 123          |
123      |   2000          | 100          | 2016-02-05   | 100             | 1500         | 123          |
123      |   1500          | 100          | 2016-02-06   | 150             | 1500         | 123          |

我需要的是这个:

ColName     |   2016-02-04   |   2016-02-05   |   2016-02-06   |
----------------------------------------------------------------
PlanMilling |   1000         |   2000         |   1500         |
MinStock    |    100         |    100         |    100         |
QtyBuilding |    200         |    100         |    150         |
QtyStock    |   1500         |   1500         |   1500         |

谁能解决我的案子?我真的需要你的帮助(抱歉我的英语不好).

Can anyone solve my case? I really need your help (sorry for my bad english)..

谢谢

推荐答案

这会在 ScheduleDate 上动态执行 PIVOT.您可以通过将 SELECT 语句重写为

This does your PIVOT dynamically on ScheduleDate. You can use this script by rewriting your SELECT statement as

SELECT <your selects> INTO #tt FROM <the rest of your query>

并使用 CREATE TABLE #tt 和 INSERT INTO #tt 语句之后的脚本部分.旋转后不要忘记删除临时表.

and use the part of the script after the CREATE TABLE #tt and INSERT INTO #tt statements. After pivotting don't forget to DROP the temporary table.

CREATE TABLE #tt(ItemNo INT,PlanMilling INT,MinStock INT,Schedule VARCHAR(10),QtyBuilding INT,QtyStock INT,ItemCode INT);
INSERT INTO #tt(ItemNo,PlanMilling,MinStock,Schedule,QtyBuilding,QtyStock,ItemCode)VALUES
    (123,1000,100,'2016-02-04',200,1500,123),
    (123,2000,100,'2016-02-05',100,1500,123),
    (123,1500,100,'2016-02-06',150,1500,123);

DECLARE @schedule_dates NVARCHAR(MAX)=STUFF((
    SELECT DISTINCT
        ','+QUOTENAME(Schedule)
    FROM
        #tt
    FOR
        XML PATH('')
    ),1,1,''
);

DECLARE @stmt NVARCHAR(MAX)=N'
    SELECT
        ColName,' +
        @schedule_dates+'
    FROM
        #tt
        UNPIVOT (
            value
            FOR ColName in (PlanMilling,MinStock,QtyBuilding,QtyStock)
        ) AS up
        PIVOT (
            MAX(value)
            FOR Schedule IN ('+@schedule_dates+')
        ) AS p;
';
EXECUTE sp_executesql @stmt;

DROP TABLE #tt;

结果:

+-------------+------------+------------+------------+
|   ColName   | 2016-02-04 | 2016-02-05 | 2016-02-06 |
+-------------+------------+------------+------------+
| MinStock    |        100 |        100 |        100 |
| PlanMilling |       1000 |       2000 |       1500 |
| QtyBuilding |        200 |        100 |        150 |
| QtyStock    |       1500 |       1500 |       1500 |
+-------------+------------+------------+------------+

这篇关于4 个连接表 SQL Server 的 Pivot 和 Unpivot的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

unpivot