how to separated One column to two with cases

user_8960494 0
Viewed 76

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

enter image description here

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
sql-server sql
5 Answers

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.

user_20677530 
select a.[batch],a.[Doc_Type],
        isnull(CASE   WHEN a.[Doc_Type]='AA' THEN convert(real,a.Qty)  END,0) as [Soaking In] ,
        isnull(CASE  WHEN a.[Doc_Type]='BB' THEN convert(real,a.Qty) END ,0)as [Soaking Out] 
FROM Transaction_Hdr a
user_52049030

I think you are looking for quantity in result table, So you should use that instead of string 'Soaking In' and 'Soaking Out' as follows 

select a.[batch],a.[Doc_Type],
SoakingOut =  
  CASE a.[Doc_Type]   
     WHEN 'BB' THEN Qty 
  END    ,

    SoakingIn =  
  CASE a.[Doc_Type]  
     WHEN 'AA' THEN Qty    
  END

 FROM #temp a
user_76989090 
BEGIN TRAN


CREATE TABLE #Data (
    Doc_Type VARCHAR(10),
    Batch INT,
    Qty DECIMAL(4,2)
    );
INSERT INTO #Data VALUES
('AA', 1, 20.5),
('BB', 2, 10  ),
('AA', 3, 6   ),
('BB', 4, 7   ),
('AA', 5, 8   );

SELECT ISNULL(CASE WHEN Doc_Type='AA'THEN CONVERT(NVARCHAR(10),QTY) END,'') Soaking_In , 
ISNULL(CASE WHEN Doc_Type='BB'THEN CONVERT(NVARCHAR(10),QTY) END,'')  Soaking_Out
FROM #Data


ROLLBACK TRAN

enter image description here

user_65505600

Use CASE() and Modulus as below, assuming that Batch is always inceremnted by 1 and Doc_Type has always those two values AA and BB in the same order:

CREATE TABLE Data (
    Doc_Type VARCHAR(10),
    Batch INT,
    Qty DECIMAL(4,2)
    );
INSERT INTO Data VALUES
('AA', 1, 20.5),
('BB', 2, 10  ),
('AA', 3, 6   ),
('BB', 4, 7   ),
('AA', 5, 8   );

SELECT D.Doc_Type, D.Batch, 
       CASE WHEN D.Batch % 2 = 0 Then 0 ELSE D.Qty END AS Soaking_In, 
       CASE WHEN D.Batch % 2 = 1 Then 0 ELSE D.Qty END AS Soaking_Out
FROM Data D;

Results:

+----------+-------+------------+-------------+
| Doc_Type | Batch | Soaking_In | Soaking_Out |
+----------+-------+------------+-------------+
| AA       |     1 |      20,50 |        0,00 |
| BB       |     2 |       0,00 |       10,00 |
| AA       |     3 |       6,00 |        0,00 |
| BB       |     4 |       0,00 |        7,00 |
| AA       |     5 |       8,00 |        0,00 |
+----------+-------+------------+-------------+

Demo

user_64266920
Related