CODEWITHSUNDEEP
sqlsql-server-2012
FritsJ
FritsJ

Reputation: 71

SQL - Case When resulting into multiple column

Hi im using this query so somehow I can merge the value into 1 column but when im using case when it results to having a multiple columns.

This is my query 

SELECT 
        SR.id,   
       CONVERT(VARCHAR(13), CAST(DR.doc_date AS DATE), 100)     as DOCDATE,

       --FOR DOC TYPE
        CASE 
            WHEN 
            SR.net_total IS NOT NULL THEN 'RENT TOTAL'
        END as [DOC TYPE],
        CASE 
            WHEN 
            SR.dell_col_charge IS NOT NULL THEN 'CHARGED'
        END as [DOC TYPE],
        CASE 
            WHEN 
             SR.CDW IS NOT NULL THEN 'CDW'
        END as [DOC TYPE],
        CASE 
            WHEN 
             SP.amount IS NOT NULL THEN 'PAYMENT'
        END as [DOC TYPE],
        --FOR DEBIT AND CREDIT
        CASE WHEN 
            SR.net_total IS NOT NULL THEN SR.net_total
            END as [DEBIT],
        CASE WHEN 
            SR.dell_col_charge IS NOT NULL THEN  SR.dell_col_charge 
            END as [DEBIT],
        CASE WHEN 
            SR.CDW IS NOT NULL THEN SR.CDW
            END as [CREDIT],
        CASE WHEN 
            SP.amount IS NOT NULL THEN SP.amount
            END as [CREDIT]

FROM [dbo].[doc_customer] DC 
LEFT JOIN [dbo].[doc_rent] DR ON DR.doc_sourced_customer_id = DC.id 
LEFT JOIN [dbo].[slip_rent] SR ON SR.doc_sourced_doc_rent_id = DR.id 
LEFT JOIN [dbo].[slip_rent_payment] SP ON SP.doc_sourced_rent_id = DR.id

WHERE DC.id = '1-1---1-1-1---1--1~1' 
AND DR.deleted = 0
AND DR.void = 0

As u can see I want my DOC TYPE to one column only same as the DEBIT and CREDIT

is there any idea or solution to this ?

Thanks

also this is my desired result

enter image description here

EDIT!

this is my result when I'm using my query enter image description here

Upvotes: 1

Views: 97

Answers (3)

SqlZim
SqlZim

Reputation: 38023

Using a case expression with multiple cases, and coalesce() to take the first non null column for debit and credit

select 
    sr.id
  , convert(varchar(13), cast(dr.doc_date as date), 100) as docdate
    --for doc type
  , case 
      when sr.net_total is not null then 'rent total'
      when sr.dell_col_charge is not null then 'charged'
      when sr.cdw is not null then 'cdw'
      when sp.amount is not null then 'payment'
    end as [doc type]
    --for debit and credit
  , coalesce(sr.net_total,sr.dell_col_charge) as [debit]
  , coalesce(sr.cdw,sp.amount) as [credit]
from [dbo].[doc_customer] dc 
  left join [dbo].[doc_rent] dr
    on dr.doc_sourced_customer_id = dc.id 
  left join [dbo].[slip_rent] sr
    on sr.doc_sourced_doc_rent_id = dr.id 
  left join [dbo].[slip_rent_payment] sp
    on sp.doc_sourced_rent_id = dr.id
where dc.id = '1-1---1-1-1---1--1~1' 
  and dr.deleted = 0
  and dr.void = 0

Upvotes: 0

Serkan Arslan
Serkan Arslan

Reputation: 13393

your query should be like

SELECT 
        SR.id,   
       CONVERT(VARCHAR(13), CAST(DR.doc_date AS DATE), 100) as DOCDATE,

       --FOR DOC TYPE
        CASE 
            WHEN SR.net_total IS NOT NULL THEN 'RENT TOTAL'
            WHEN SR.dell_col_charge IS NOT NULL THEN 'CHARGED'
            WHEN SR.CDW IS NOT NULL THEN 'CDW'
            WHEN SP.amount IS NOT NULL THEN 'PAYMENT'
        END as [DOC TYPE],
        --FOR DEBIT AND CREDIT
        CASE 
            WHEN SR.net_total IS NOT NULL THEN SR.net_total
            WHEN SR.dell_col_charge IS NOT NULL THEN  SR.dell_col_charge 
        END as [DEBIT],
        CASE 
            WHEN SR.CDW IS NOT NULL THEN SR.CDW
            WHEN SP.amount IS NOT NULL THEN SP.amount
        END as [CREDIT]

FROM [dbo].[doc_customer] DC 
LEFT JOIN [dbo].[doc_rent] DR ON DR.doc_sourced_customer_id = DC.id 
LEFT JOIN [dbo].[slip_rent] SR ON SR.doc_sourced_doc_rent_id = DR.id 
LEFT JOIN [dbo].[slip_rent_payment] SP ON SP.doc_sourced_rent_id = DR.id

WHERE DC.id = '1-1---1-1-1---1--1~1' 
AND DR.deleted = 0
AND DR.void = 0

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Use a single case for each expression:

  (CASE WHEN  SR.net_total IS NOT NULL THEN 'RENT TOTAL'
        WHEN  SR.dell_col_charge IS NOT NULL THEN 'CHARGED'
        . . .
   END) as [DOC TYPE],
  (CASE WHEN SR.net_total IS NOT NULL THEN SR.net_total
        WHEN SR.dell_col_charge IS NOT NULL THEN  SR.dell_col_charge 
        . . .
   END) as [DEBIT],
  . . .

However, this assumes that each row has only one doc type.

Upvotes: 1

Related Questions