Sql-server – Unpivot multiple similar columns

sql serverunpivot

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?

Best Answer

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