Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MSSQL Query - Return Multiple Records for Each Row

Posted on 2012-03-20
4
Medium Priority
?
372 Views
Last Modified: 2012-03-21
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
0
Comment
Question by:FamousMortimer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 25

Expert Comment

by:jogos
ID: 37742114
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 37742403
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
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 37742441
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
 
LVL 10

Author Closing Comment

by:FamousMortimer
ID: 37748267
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

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question