Unpivot multiple similar columns

Question

I am trying to normalize a few tables. Here is one of my tables

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

I want to get it to

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

I have been trying to run with unpivot - how do make sure I have the ordinality (column2) info?

Scott Hodgin - Retired · Accepted Answer · 2018-11-16T16:18:31.837

I started with the information in Aaron Bertrand's awesome post Use SQL Server's UNPIVOT operator to dynamically normalize output. You should read the information in that post as Aaron does a great job of explaining the process.

I had to make some changes to Aaron's process due the fact that you had more columns that what Aaron had in his example(s).

--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      |

score 0 · Answer 2 · answered Nov 16 '18 at 14:48

Given that you have three columns that you need to unpivot, and the structure of the source table, I wouldn't bother with unpivot and would just do the following.

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;

Unpivot multiple similar columns

2 Answers2