SQL split one column into two columns based on values and use columns

Question

Table: ProductionOrder

Id     Ordernumber     Lotsize
1        Order1            50
2        Order 2           75
3        WO-order1         1
4        WO-order2         1

Table: history

Id          ProductionOrderID          Completed  
1            3                         1
2            3                         1
3            4                         1
4            4                         1

Table: ProductionOrderDetail

ID   ProductionOrderID     ProductionOrderDetailDefID   Content
1     1                    16                            50
2     1                    17                            7-1-2018
3     2                    16                            75
4     2                    17                            7-6-2018

Start of my code:

 Select p.ID, p.OrderNumber, 
     Case productionOrderDetailDefID
         Where(Select pd1.productionOrderDetailDefID where ProductionOrderDetialDefID = 16) then min(pd1.content)
 from ProductionOrder p
     Left join History h1 on  p.id = h1.productionOrderID
     Left Join ProductionOrderDetail pd1 on p.ID = ProductionOrderID

The result in trying to get is

Id      Ordernumber     Lotsize   Productionorder        Completed
1       Order1            50           WO-order1           2
2       Order 2           75           WO-order2           2

Any help would be appreciated.

Some further explanation of your intentions would be appreciated. In your result you seem to combine (JOIN) some records of your first table based on their ordernumber. Does the history table play a role in the JOIN process? — Carsten Massmann
– Carsten Massmann, Commented Jul 26, 2018 at 5:40
I think you are in the very beginning level of SQL so please do some study and research about it. Above you mentioned are objects which is called table not database please correct it. The requirement is also not so meaningful. — Shushil Bohara
– Shushil Bohara, Commented Jul 26, 2018 at 5:42
You need to explain in more details the logic of grouping and pivoting the data. Or you need to fix the issues with the current one. Otherwise, you will continue to get wrong answers. — gotqn
– gotqn, Commented Jul 26, 2018 at 6:09

Pramit Sawant · Accepted Answer · 2018-07-26 05:52:41Z

2

Try this

SELECT ordernumber,lotsize,Ordernumber,count(Ordernumberid) 
       FROM productionorder inner join history on productionorder.id = history.Ordernumberid 
       GROUP BY Ordernumber;

answered Jul 26, 2018 at 5:52

Pramit Sawant

8211 gold badge11 silver badges17 bronze badges

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

Comments

Edward · Accepted Answer · 2018-07-26 05:42:22Z

1

A bit of weird joins going on here. You should add this to a SQL fiddle so that we can see our work easier.

A link to SQL fiddle: http://sqlfiddle.com/

Here is my first attempt

SELECT
        po.id
    ,   po.ordernumber
    ,   po.lotsize 
    ,   po2.productionorder 
    ,   SUM(h.completed) 
FROM productionorder as po 
INNER JOIN history as h 
    ON h.id = po.id 
INNER JOIN prodcuctionorder as po2 
    ON po2.ordernumberid = h.ordernumberid 
WHERE po.id NOT EXISTS IN ( SELECT ordernumberid FROM history ) 
GROUP BY 
        po.id 
    ,   po.ordernumber 
    ,   po.lotzise 
    ,   po2.productionorder

How far does that get you?

answered Jul 26, 2018 at 5:42

Edward

7624 silver badges18 bronze badges

Collectives™ on Stack Overflow

SQL split one column into two columns based on values and use columns

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related