4 个连接表 SQL Server 的 Pivot 和 Unpivot [英] Pivot and Unpivot for 4 joined table SQL Server

问题描述

我使用的是 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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！