Normalize a row of data in a query - Turn one row into many

user_8946858 0
Viewed 472

Is there a better way of doing this , I am using SQL Server 2012.

I have a table built by as vendor that looks like the below:

Customer, Date, Desc1, Qty1, Price1, Desc2, Qty2, Price2, Desc3, Qty3, Price3

I want a result set that returns this;

Customer, Date, Desc, Qty, Price

I am doing it today by the following solution , anyone have something better or more efficient? 

Select Customer, Date, Desc1, Qty1, Price1
UNION ALL
Select Customer, Date, Desc2, Qty2, Price2
UNION ALL
Select Customer, Date, Desc3, Qty3, Price3
sql-server-2012 sql
2 Answers

Here is an alternative for SQL Server; it uses cross apply and values and it is an efficient and very flexible substitute for the unpivot command and is really useful for "normalizing" data.

select Customer, Date, ca.description, ca.qty, ca.price
from YourTable
cross apply (
   values
      (Desc1, Qty1, Price1)
    , (Desc2, Qty2, Price2)
    , (Desc3, Qty3, Price3)
    , (Desc4, Qty4, Price4)
    ) ca (description, qty, price)

NB: Each row within the values area forms a new row of output, so when laid out as you see above it is visually similar to the final layout.

For more details on this see: Spotlight on UNPIVOT, Part 1 (Brad Schultz)

user_20677530

The UNION ALL method works just fine. However, you may have different performance or readability with CROSS APPLY:

SELECT t1.Customer,
    t1.[Date],
    x.[Desc],
    x.Qty,
    x.Price
FROM UnnamedTable t1
CROSS APPLY (
    VALUES (Desc1, Qty1, Price1),
        (Desc2, Qty2, Price2),
        (Desc3, Qty3, Price3)
    ) x ([Desc], Qty, Price);

That's assuming that each repeating group is really the same data type. That is, Qty1, Qty2, and Qty3 are all int (or similar), and so on.

Alternately, you could use an UNPIVOT expression, but, honestly, I find UNPIVOT's syntax to be extremely arcane.

user_6968080
Related