Solved

SQL - Generate 2 rows from 1

Posted on 2013-06-24
10
257 Views
Last Modified: 2013-07-01
I have data as follows:

ID     Desc        Hrs      Rate      Qty
1       Task 1      0          10          1
2       Task 2      1          20          2
3       Task 3      0          5            1

If a row has Hrs and Qty I need to return row for each one.  So the results should be

Desc       Rate     Amt    Total
Task 1     10        1         10
Task 2     20        1         20
Task 2     20        2         40
Task 3     5          1         5

Thanks
0
Comment
Question by:CipherIS
[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
  • 3
  • 3
  • 2
10 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39271366
this result:
DESC	RATE	AMT	TOTAL
Task 1	10	1	10
Task 2	20	1	20
Task 2	20	2	40
Task 3	5	1	5

--from this code:

select
  [desc]
, Rate
, Amt
, amt*rate Total
from yourtable
cross apply (
       values
              (hrs)
            , (qty)
            ) ca (Amt)
where amt > 0

Open in new window

http://sqlfiddle.com/#!3/3b455/8
0
 
LVL 45

Accepted Solution

by:
Kent Olsen earned 500 total points
ID: 39271378
A simpler query would be:


SELECT * FROM mytable WHERE qty <> 0
UNION ALL
SELECT * FROM mytable WHERE hours <> 0


Kent
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39271565
there is only one table pass with the cross apply approach, but unioning would be an alternative when the fields are expanded

SELECT DESC, RATE, qty as AMT, qty*rate as TOTAL
FROM mytable WHERE qty <> 0
UNION ALL
SELECT DESC, RATE, hours as AMT, hours*rate as TOTAL
FROM mytable WHERE hours <> 0

-- compared to:
select [desc], Rate, Amt, amt*rate Total
from yourtable
cross apply ( values (hrs), (qty)  ) as CA (Amt)
where amt > 0
0
Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

 
LVL 32

Expert Comment

by:awking00
ID: 39271776
I'm not sure we have enough data to assure the desired results you seek. What would you expect with data like the following?:
ID     Desc        Hrs      Rate      Qty
1       Task 1      0          10          1
2       Task 2      2          20          3
3       Task 3      0          5            1
Also, can quantity ever be 0?
0
 
LVL 32

Expert Comment

by:awking00
ID: 39272474
How in the world does that solution produce these results?
>> So the results should be
Desc       Rate     Amt    Total
Task 1     10        1         10
Task 2     20        1         20
Task 2     20        2         40
Task 3     5          1         5<<
SQL> select * from yourtbl;
        ID DESCR         HRS       RATE        QTY
---------- ------ ---------- ---------- ----------
         1 Task 1          0         10          1
         2 Task 2          1         20          2
         3 Task 3          0          5          1
SQL> SELECT * FROM yourtbl WHERE qty <> 0
  2  UNION ALL
  3  SELECT * FROM yourtbl WHERE hrs <> 0;

        ID DESCR         HRS       RATE        QTY
---------- ------ ---------- ---------- ----------
         1 Task 1          0         10          1
         2 Task 2          1         20          2
         3 Task 3          0          5          1
         2 Task 2          1         20          2
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 39272734
I have no idea what the database looks like, but the question implies that HRS and QTY are both entities affected by rate.  

If my assumption is correct, it's an incremental step to go from:

SELECT * FROM mytable WHERE qty <> 0
UNION ALL
SELECT * FROM mytable WHERE hours <> 0

to:

SELECT rate, qty as amt, rate * qty as Total FROM mytable WHERE qty <> 0
UNION ALL
SELECT rate, amt, rate * amt as TOTAL FROM mytable WHERE hrs <> 0


That answers the question.  PorletPaul's solution does too.  On a large table the cross apply is probably more efficient.  Both solutions get to the same place.


Kent
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39273559
It is my view that the accepted answer is incomplete. Without amendment that answer does not produce the expected results for the given inputs.

Whilst the technique of using 'union all' can be applied it required the incremental step to be a full answer. Additionally the use of 'union all' is less efficient than the first given correct answer, but perhaps the 'union all' technique was chosen because of familiarity instead of efficiency.

Note it is quite possible the asker could produce the needed incremental step without further assistance - in which case I suggest ID: 39271378 as accepted answer zero points and ID: 39271378 as an assist with points. To be honest I'm only disappointed that the more efficient technique was bypassed.
0
 
LVL 32

Expert Comment

by:awking00
ID: 39289812
>>Whilst I accept the answer might be considred "incomplete"<<
Without any explanation from the asker, I would consider the answer to be wrong rather than incomplete.
0

Featured Post

Get Actionable Data from Your Monitoring Solution

Your communication platform is only as good as the relevance of the information you send. Ensure your alerts get to the right people every time with actionable responses. Create escalation rules that ensure everyone follows the process and nothing is left to chance.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

696 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