0

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?

1

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      |
  • This is cool stuff- the Replace was like Aha! Thank you. – abbhey Nov 16 '18 at 19:20
  • @abbhey - Glad to hear it's working for you! – Scott Hodgin Nov 16 '18 at 19:24
1

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;
  • Thanks for the answer. Guess it was a problem with my edits in the question my bad-................ BUT My problem is I have many columns like Order1xxx, Order1xx, Order2xxx, Order2xxx, Order3xxx, Order3xx., Order4xxx, Order4xxx. I did start down this path - but it will be too tedious and not so clean for me.......... I will update the question to reflect this. – abbhey Nov 16 '18 at 15:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.