how to separated One column to two with cases

Question

I have a table and I would want to separate the data to multiple columns, how i can do it ?

I tried this:

 select a.[batch],a.[Doc_Type],
    Soaking Out =  
      CASE a.[Doc_Type]   
         WHEN 'BB' THEN 'Soaking Out'  
      END,

        Soaking In =  
      CASE a.[Doc_Type]  
         WHEN 'AA' THEN 'Soaking In'    
      END,

     FROM Transaction_Hdr a JOIN Transaction_dtl b
on a.Doc_Number=b.Doc_Number

Answer 1

Your original query would output the strings 'soaking in' or 'soaking out', but what is needed in those case expressions (after then) is the column [Qty] and it is that value which will be returned from the case expression.

What I don't know is which table [Qty] comes from but I assume it is the detail table (b) otherwise there isn't much point in joining that detail table.

SELECT
      a.[Doc_Type]
    , a.[batch]
    , CASE a.[Doc_Type] WHEN 'BB' THEN b.Qty END [soaking out]
    , CASE a.[Doc_Type] WHEN 'AA' THEN b.Qty END [soaking in]
FROM Transaction_Hdr a
JOIN Transaction_dtl b ON a.Doc_Number = b.Doc_Number
ORDER BY 
      a.[Doc_Type]
    , a.[batch]

But: a "detail" table and a "header" table usually indicates many rows of detail for a single header. So you might need a SUM() and GROUP BY

SELECT
      h.[Doc_Type]
    , h.[batch]
    , SUM(CASE h.[Doc_Type] WHEN 'BB' THEN d.Qty END) [soaking out]
    , SUM(CASE h.[Doc_Type] WHEN 'AA' THEN d.Qty END) [soaking in]
FROM Transaction_Hdr h
JOIN Transaction_dtl d ON h.Doc_Number = d.Doc_Number
GROUP BY
      h.[Doc_Type]
    , h.[batch]
ORDER BY 
      h.[Doc_Type]
    , h.[batch]

Note I have now used aliases "h" = "header" and "d" = "detail" as I am really not keen of aliases that rely on a sequence within the query (as that sequence can get messed with very easily). I find it way easier for an alias to easily identify its associated table by "first letter of each word in a table's name" or similar.