CODEWITHSUNDEEP
sqlsql-server-2012
Kinyanjui Kamau
Kinyanjui Kamau

Reputation: 1936

Transforming a column into a row

I have the following query:

SELECT DISTINCT 
    T0.DocNum, T0.Status, T1.ItemCode, T2.ItemName, 
    T1.PlannedQty, T0.PlannedQty AS 'Net Quantity'
FROM 
    OWOR T0  
INNER JOIN 
    WOR1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN 
    OITM T2 ON T0.ItemCode = T2.ItemCode
WHERE 
    T0.Status = 'L' AND 
    T1.ItemCode IN ('BYP/RM/001', 'BYP/RM/002', 'BYP/RM/003', 'BYP/RM/004','BILLET') AND 
    T2.ItmsGrpCod = 111 AND 
    (T0.PostDate BETWEEN (SELECT Dateadd(month, Datediff(month, 0, {?EndDate}), 0)) AND {?EndDate})

that returns data like:

enter image description here

Explanation:

To make 10 MM steel bars, billets are used as raw materials. Any ItemCode 'BYP%' is part of solid wastage. The net quantity for every DocNum is the amount of 'MM' steel produced by weight. For example, for DocNum 348, the following are used as inputs:

enter image description here

However for that DocNum, 147.359 of 10 MM steel was produced, meaning the missing 3.52 (150.879 - 147.359) is burned loss (not solid).

How do I modify the query such that for each DocNum, the query returns:

enter image description here

Upvotes: 2

Views: 76

Answers (3)

Nikhil Vartak
Nikhil Vartak

Reputation: 5117

See the SQL code below. 

declare @input_tbl table ( doc_num int, item_code varchar(15), item_name varchar(10), planned_qty decimal(15,9), net_qty decimal(15,9) )
declare @ouput_tbl table ( doc_num int, item varchar(15), name varchar(10), quantity decimal(15,9) )

-- Inserting sample data shown by you. You will have to replace this with your 1st query.
insert into @input_tbl values (348, 'BILLET' , '10MM', 154.629000, 147.359000)
insert into @input_tbl values (348, 'BYP/RM/001' , '10MM', -1.008000, 147.359000)
insert into @input_tbl values (348, 'BYP/RM/003' , '10MM', -1.569000, 147.359000)
insert into @input_tbl values (348, 'BYP/RM/004' , '10MM', -1.173000, 147.359000)

-- This stores unique doc numbers from input data
declare @doc_tbl table ( id int identity(1,1), doc_num int )
insert into @doc_tbl select distinct doc_num from @input_tbl

-- Loop through each unique doc number in the input data
declare @doc_ctr int = 1
declare @max_doc_id int = (select max(id) from @doc_tbl)

while @doc_ctr <= @max_doc_id
begin
    declare @doc_num int
    declare @planned_qty_total decimal(15,9)
    declare @net_qty decimal(15,9)
    declare @burned_loss decimal(15,9)
    declare @item_name varchar(15)

    select @doc_num = doc_num from @doc_tbl where id = @doc_ctr
    select @planned_qty_total = sum(planned_qty) from @input_tbl where doc_num = @doc_num
    select distinct @item_name = item_name, @net_qty = net_qty from @input_tbl where doc_num = @doc_num
    select @burned_loss = @planned_qty_total - @net_qty

    -- 'Union' is also fine but that won't sort the records as desired
    insert into @ouput_tbl select doc_num, item_code, item_name, planned_qty from @input_tbl
    insert into @ouput_tbl select @doc_num, 'BurnLoss', @item_name, @burned_loss * -1
    insert into @ouput_tbl select @doc_num, 'Net', @item_name, @net_qty

    set @doc_ctr = @doc_ctr + 1
end

select * from @ouput_tbl

Ouput:

docnum  item        name    quantity
348     BILLET      10MM    154.629000000
348     BYP/RM/001  10MM    -1.008000000
348     BYP/RM/003  10MM    -1.569000000
348     BYP/RM/004  10MM    -1.173000000
348     BurnLoss    10MM    -3.520000000
348     Net         10MM    147.359000000

Upvotes: 2

Tempestas Ludi
Tempestas Ludi

Reputation: 1165

You should use a UNION. I guess

(/*your original query*/)

UNION

SELECT DISTINCT
    T0.DocNum, T0.Status, 'BurnLoss' AS ItemCode, T2.ItemName,
    SUM(T1.PlannedQuantity)-T0.PlannedQuantity AS PlannedQuantity, T0.PlannedQuantity AS 'Net Quantity'
FROM 
    OWOR T0  
INNER JOIN 
    WOR1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN 
    OITM T2 ON T0.ItemCode = T2.ItemCode
WHERE 
    T0.Status = 'L' AND 
    T1.ItemCode IN ('BYP/RM/001', 'BYP/RM/002', 'BYP/RM/003', 'BYP/RM/004','BILLET') AND 
    T2.ItmsGrpCod = 111 AND 
    (T0.PostDate BETWEEN (SELECT Dateadd(month, Datediff(month, 0, {?EndDate}), 0)) AND {?EndDate})
GROUP BY T0.DocNum

UNION

SELECT DISTINCT
    T0.DocNum, T0.Status, 'Net' AS ItemCode, T2.ItemName,
    T0.PlannedQuantity, T0.PlannedQuantity AS 'Net Quantity'
FROM 
    OWOR T0  
INNER JOIN 
    WOR1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN 
    OITM T2 ON T0.ItemCode = T2.ItemCode
WHERE 
    T0.Status = 'L' AND 
    T1.ItemCode IN ('BYP/RM/001', 'BYP/RM/002', 'BYP/RM/003', 'BYP/RM/004','BILLET') AND 
    T2.ItmsGrpCod = 111 AND 
    (T0.PostDate BETWEEN (SELECT Dateadd(month, Datediff(month, 0, {?EndDate}), 0)) AND {?EndDate})
GROUP BY T0.DocNum

should work, although it might even be better to do some of this in a language like PHP, because now every bit of information is fetched twice (and the query becomes two times as long).

Upvotes: 2

Stavr00
Stavr00

Reputation: 3314

Use a UNION ALL to combine your original query with a second BurnLoss query of GROUP BY T0.DocuNum that computes SUM(T1.PlannedQty) - T0.PlannedQty and a third Net query to show T0.PlannedQty

Upvotes: 0

Related Questions