MSSQL Query - Return Multiple Records for Each Row

Hi Experts,

I have a table set up similar to the following

ID | Order | Qty1 | Qty2 | Qty3 | Qty4

I need a query to return four records for each row like this:

ID | Order | Qty1
ID | Order | Qty2
ID | Order | Qty3
ID | Order | Qty4

but only return those whose quantity is not zero

Thanks
-Jeremy
LVL 10
FamousMortimerAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
That avoids scanning the table multiple times.

But UNPIVOT will probably have the best performance.


SELECT
    ID, [Order], Qty
FROM (
    SELECT ID, [Order], Qty1, Qty2, Qty3, Qty4
    FROM dbo.tablename
) AS base_data
UNPIVOT (
    Qty FOR Qty_List IN
       ([Qty1], [Qty2], [Qty3], [Qty4])
    )
AS unpvt
0
 
jogosCommented:
select id,order ,qty1 from tableA when  qty1 is not null
union all
select id,order ,qty2 from tableA when  qty2 is not null
union all
select id,order ,qty3 from tableA when  qty3 is not null
union all
select id,order ,qty4 from tableA when  qty4 is not null

Open in new window

0
 
Scott PletcherSenior DBACommented:
SELECT
    ID, Order,
    CASE whichQty
        WHEN 1 THEN Qty1
        WHEN 2 THEN Qty1
        WHEN 3 THEN Qty1
        WHEN 4 THEN Qty1
    END AS Qty
FROM dbo.tablename
CROSS JOIN (
    SELECT 1 AS whichQty UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
    SELECT 4
) AS whichQtys
WHERE
    CASE whichQty
        WHEN 1 THEN Qty1
        WHEN 2 THEN Qty1
        WHEN 3 THEN Qty1
        WHEN 4 THEN Qty1
    END <> 0
0
 
FamousMortimerAuthor Commented:
Exactly what I was looking for... I know I could have used Unions but this was a quick, powerful, and elegant solution.

I was not familiar with unpivot, so thanks a lot!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.