How do I create this output in SQL

Question

I have this information.

Table OrderTotals

Order  Value
A         10
B         20
C         15

Table Orderdetails

Order  Line Description
A        11 Red
B        24 Blue
B        25 Green
B        28 Yellow
C        17 Green

And I want this output

Order Line Description Value
A       11 Red         10
B       24 Blue        20
B       25 Green
B       28 Yellow
C       17 Green       15

Is this possible? And if yes, how?

This sounds like a problem you should try to solve outside your SQL Server. You want to portray order line details with the order total, but only for the first record of said details. Makes little sense to me other than terribly odd design. To answer your question, it is certainly possible though. If you use ROW_NUMBER() sorted on your Line and use a CASE WHEN if it's 1 to only then select the order total with a subquery you'd get there. — Jens
– Jens, Commented Oct 24, 2016 at 11:08

Gordon Linoff · Accepted Answer · 2016-10-24 11:13:00Z

3

You would seem to want a left join:

select od.*,
       (case when row_number() over (partition by od.[order] order by line) = 1
             then ot.value
        end) as value
from orderdetails od left join
     ordertotals ot
     on od.[order] = ot.[order]
order by od.[order], od.line;

I'm not sure why you would want value on only one line, but this should achieve that goal.

answered Oct 24, 2016 at 11:13

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

PSVSupporter Over a year ago

Thanks, this is what I want!

Tharunkumar Reddy · Accepted Answer · 2016-10-24 11:15:34Z

1

Try this

SELECT A.[Order],
       CASE
         WHEN Row_number()
                OVER(
                  PARTITION BY A.[ORDER]
                  ORDER BY A.[ORDER]) = 1 THEN VALUE
         ELSE NULL
       END AS VALUE,
       B.[Line Description]
FROM   TABLE1 A
       LEFT JOIN Table2 B
              ON A.[Order] = B.[Order]

answered Oct 24, 2016 at 11:15

Tharunkumar Reddy

2,81320 silver badges33 bronze badges

Comments

Mr. Bhosale · Accepted Answer · 2016-10-24 11:12:47Z

0

Try below query.

            select 
            od.Order,
            od.Line ,
            od.Description,
            ot.Value

            from
            OrderTotals OT 
            join Orderdetails OD on OT.order=od.order

if you have any query please let us know

answered Oct 24, 2016 at 11:12

Mr. Bhosale

3,1161 gold badge19 silver badges37 bronze badges

Comments

neer · Accepted Answer · 2016-10-24 11:13:19Z

0

You can as the below:

SELECT  
    *
FROM    
    Orderdetails X LEFT JOIN
    (
        SELECT
            *
        FROM
            OrderTotals A INNER JOIN
            (SELECT OD.Order, MIN(OD.Line) MinLine FROM Orderdetails OD GROUP BY OD.Order) B ON A.Order = B.Order

    ) Y ON X.Order = Y.Order AND X.Line = Y.MinLine

answered Oct 24, 2016 at 11:13

neer

4,0926 gold badges22 silver badges34 bronze badges

Collectives™ on Stack Overflow

How do I create this output in SQL

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related