CODEWITHSUNDEEP
sqlsql-server
Doonie Darkoo
Doonie Darkoo

Reputation: 1495

Divide column into 2

I have a table where I'm storing Allowances and Deductions. If the BaseCode is 1 then it is an allowance and if BaseCode is 2 then it is deduction. I am trying to get the column transformed into 2 columns.

Allowance Deduction Something Something

SELECT (
        SELECT INN.FullName AS Benefit
        FROM [AppCNF].[tbl_AllowanceOrBenefitType] INN
        WHERE INN.BASECODE = 1
            AND INN.ID = OUTR.ID
        )
    , FullName AS Deductions
FROM [AppCNF].[tbl_AllowanceOrBenefitType] OUTR
WHERE BASECODE = 2

This is what I have tried so far but it is giving NULL for Allowances.

Upvotes: 1

Views: 67

Answers (2)

Vladimir Baranov
Vladimir Baranov

Reputation: 32695

Not sure what exactly you are after, but most likely you can use CASE:

SELECT
    ID
    ,CASE WHEN BASECODE = 1 THEN FullName END AS Benefit
    ,CASE WHEN BASECODE = 2 THEN FullName END AS Deductions
    -- ,... other columns ...
FROM [AppCNF].[tbl_AllowanceOrBenefitType]
;

Upvotes: 5

EzLo
EzLo

Reputation: 14189

You can use CASE to condition a result from a column.

SELECT 
    IsAllowance = CASE WHEN OUTR.BASECODE = 1 THEN 'Yes' END,
    IsDeduction = CASE WHEN OUTR.BASECODE = 2 THEN 'Yes' END,
    OUTR.*
FROM 
    [AppCNF].[tbl_AllowanceOrBenefitType] OUTR

Upvotes: 1

Related Questions