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

Question

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

what you have is fine, if you have a common where clause though, you could put this in a with statement — Ab Bennett, Nov 16 '17 at 22:18
The *best* solution is to fix your bad table schema. This is not even 1NF: you have repeating groups. Your table should look like the output of your query, perhaps with the inclusion of a `line_number` column. — Bohemian, Nov 16 '17 at 22:24
Alternatives do exist, but it depends on the type of database you are using. Please **always** tag your questions with the database (e.g. Sybase? MySQL? SQlL Serer? Oracle? ...... more) — Paul Maxwell, Nov 16 '17 at 22:29
The OP does mention a vendor, and so may not have control over the table's structure (awful as it is). — Pat Jones, Nov 16 '17 at 22:40
Correct...it is a vendor table so I am unable to modify it. As it relates to the solution provided...my data actually has 5 columns duplicated over 11 instances....so the Cross Apply in performance cut my query time in half. Perfect! — JuniorDeveloper, Nov 20 '17 at 14:48

score 2 · Answer 1 · answered Nov 16 '17 at 22:39

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)

score 1 · Answer 2 · answered Nov 16 '17 at 22:39

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.

ditto the unpivot = arcane comment :) – Paul Maxwell Nov 16 '17 at 22:42 — Paul Maxwell, Nov 16 '17 at 22:42

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

2 Answers2