SQL - Case When resulting into multiple column

Question

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

EDIT!

this is my result when I'm using my query

I already tried using single case but the problem is when the first when succeeds it wont display the other doc type. u can see on my desired result. I really need help been stuck in this problem for 8 hours. — FritsJ
– FritsJ, Commented Oct 20, 2017 at 20:04

Gordon Linoff · Accepted Answer · 2017-10-20 19:56:34Z

1

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.

answered Oct 20, 2017 at 19:56

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

FritsJ Over a year ago

I have asked the same problem last thursday and u answered correctly but the problem of that query is when I use that on my tableadapter it wont work. this is the link stackoverflow.com/questions/46809494/…

Serkan Arslan · Accepted Answer · 2017-10-20 19:57:05Z

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

SqlZim · Accepted Answer · 2017-10-20 19:58:37Z

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

Collectives™ on Stack Overflow

SQL - Case When resulting into multiple column

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related