主页 / dba / 问题 / 222681
Accepted
abbhey
Asked: 2018-11-16 16:50:52 +0800 CST

取消透视多个相似的列

  • 772

我正在尝试规范化一些表格。这是我的一张桌子

 SalesID|Order1Name|Order1Date|Order1Amt|Order2Name|Order2Date|Order2Amt|Order3Name......Order10xxxx
 ---------------------------------------------------------------------------
1001     | first   | 1/1/18   | 111.00  |  second  | 2/1/18   | 222.00

我想得到它

SalesID |OrderNum  |  OrderName  |  OrderDate  |  OrderAmt|
------------------------------------------------------------
 1001      1          first          1/1/18         111.00
 1001      2          second         2/1/18         222.00
 1001      .
 1001      . 
 1001      10

我一直在尝试使用 unpivot 运行 - 如何确保我有序数（column2）信息？

sql-server unpivot

2 个回答

  1. Best Answer

    我从Aaron Bertrand 的超棒帖子Use SQL Server's UNPIVOT operator to dynamic normalize output中的信息开始。您应该阅读该帖子中的信息，因为 Aaron 很好地解释了该过程。

    我不得不对 Aaron 的流程进行一些更改，因为您的列比 Aaron 在他的示例中的列更多。

    --demo setup
drop table if exists table1;
go
CREATE TABLE Table1
    (SalesID int, Order1Name varchar(10), Order1Date date, Order1Amt int, Order2Name varchar(10), Order2Date date, Order2Amt int)
;

INSERT INTO Table1
    (SalesID, Order1Name, Order1Date, Order1Amt, Order2Name, Order2Date, Order2Amt)
VALUES
    (1001, 'first', '2018-01-01', 111.00, 'second', '2018-02-01', 222.00)
;

SELECT 
    SalesID, 
    ROW_NUMBER() over(partition by SalesID order by OrderDate) as OrderNum,
    OrderName, 
    OrderDate, 
    OrderAmt
FROM 
(
  SELECT SalesID, OrderName, OrderDate, OrderAmt,OrderNames,
    idon = replace(replace(OrderNames,'Order',''),'Name',''),
    idod = replace(replace(OrderDates,'Order',''),'Date',''),
    idoa = replace(replace(OrderAmts,'Order',''),'Amt','')
  FROM
  (
    SELECT SalesID, [Order1Name], [Order2Name], [Order1Date], [Order2Date], [Order1Amt], [Order2Amt]
    FROM dbo.Table1
  ) AS cp
  UNPIVOT 
  (
    OrderName FOR OrderNames IN ( [Order1Name], [Order2Name])
  ) AS OrderName
  UNPIVOT 
  (
    OrderDate FOR OrderDates IN ( [Order1Date], [Order2Date])
  ) AS OrderDate
  UNPIVOT
  (
    OrderAmt FOR OrderAmts IN ( [Order1Amt], [Order2Amt])
  ) AS OrderAmt
) AS x
WHERE idod = idon and idoa = idon;

    | SalesID | OrderNum | OrderName | OrderDate  | OrderAmt |
|---------|----------|-----------|------------|----------|
| 1001    | 1        | first     | 2018-01-01 | 111      |
| 1001    | 2        | second    | 2018-02-01 | 222      |
    • 2

  2. 鉴于您需要取消透视的三列以及源表的结构，我不会为取消透视而烦恼，只需执行以下操作。

    DECLARE @SourceTable TABLE 
    (
    SalesID INT NOT NULL PRIMARY KEY
    , Order1Name VARCHAR(10) NOT NULL
    , Order1Date DATE NOT NULL
    , Order1Amt DECIMAL(10,2) NOT NULL
    , Order2Name VARCHAR(10) NOT NULL
    , Order2Date DATE NOT NULL
    , Order2Amt DECIMAL(10,2) NOT NULL
    );

INSERT INTO @SourceTable
(SalesID, Order1Name, Order1Date, Order1Amt
    , Order2Name, Order2Date, Order2Amt)
VALUES
(1001, 'first', '1/1/2018', 111.00
    , 'second', '2/1/2018', 222.00);

SELECT SalesID
    , Order1Name
    , Order1Date
    , Order1Amt
    , Order2Name
    , Order2Date
    , Order2Amt 
FROM @SourceTable;

;WITH CTE_Orders AS
    (
    SELECT S.SalesID
        , 1 AS OrderNum
        , Order1Name AS OrderName
        , Order1Date AS OrderDate
        , Order1Amt AS OrderAmt
    FROM @SourceTable AS S
    UNION ALL
    SELECT S.SalesID
        , 2 AS OrderNum
        , Order2Name AS OrderName
        , Order2Date AS OrderDate
        , Order2Amt AS OrderAmt
    FROM @SourceTable AS S
    )
SELECT SalesID
    , OrderNum
    , OrderName
    , OrderDate
    , OrderAmt 
FROM CTE_Orders
ORDER BY SalesID
    , OrderNum;
    • 0

相关问题