How to merge two columns from CASE STATEMENT of DIFFERENT CONDITION

Question

My expected result should be like

----invoiceNo----
T17080003,INV14080011

But right now, I've come up with following query.

SELECT AccountDoc.jobCode,AccountDoc.shipmentSyskey,AccountDoc.docType,    
    CASE AccountDoc.docType
    WHEN 'M' THEN
    JobInvoice.invoiceNo
    WHEN 'I' THEN
    (STUFF((SELECT ', ' + RTRIM(CAST(AccountDoc.docNo AS VARCHAR(20)))
     FROM AccountDoc LEFT OUTER JOIN JobInvoice
     ON AccountDoc.principalCode = JobInvoice.principalCode AND 
     AccountDoc.jobCode = JobInvoice.jobCode
     WHERE (AccountDoc.isCancelledByCN = 0)
          AND (AccountDoc.docType = 'I')
          AND (AccountDoc.jobCode = @jobCode)
          AND (AccountDoc.shipmentSyskey = @shipmentSyskey)
          AND (AccountDoc.principalCode = @principalCode) FOR XML 
          PATH(''), TYPE).value('.','NVARCHAR(MAX)'),1,2,' '))
    END AS invoiceNo

FROM AccountDoc LEFT OUTER JOIN JobInvoice
ON JobInvoice.principalCode = AccountDoc.principalCode AND 
JobInvoice.jobCode = AccountDoc.jobCode
WHERE (AccountDoc.jobCode = @jobCode)
        AND (AccountDoc.isCancelledByCN = 0)
        AND (AccountDoc.shipmentSyskey = @shipmentSyskey)
        AND (AccountDoc.principalCode = @principalCode)

OUTPUT:

----invoiceNo----
T17080003
INV14080011

Explanation:

I want to select docNo from table AccountDoc if AccountDoc.docType = I. Or select invoiceNo from table JobInvoice if AccountDoc.docType = M.

The problem is what if under same jobCode there have 2 docType which are M and I, how I gonna display these 2 invoices?

Answer 1

You can achieve this by using CTE and FOR XML. below is the sample code i created using similar tables you have -

Create table #AccountDoc (
id  int ,
docType char(1),
docNo  varchar(10)
)

Create table #JobInvoice (
id  int ,
invoiceNo varchar(10)
)

insert into #AccountDoc
select 1 , 'M' ,'M1234'   
union all  select 2 , 'M' ,'M2345' 
union all  select 3 , 'M' ,'M3456' 
union all  select 4 , 'I' ,'I1234' 
union all  select 5 , 'I' ,'I2345' 
union all  select 6 , 'I' ,'I3456' 

insert into #JobInvoice
select 1 , 'INV1234'   
union all  select 2 , 'INV2345' 
union all  select 3 ,  'INV3456' 

select  * 
from #AccountDoc t1 left join #JobInvoice t2
on t1.id = t2.id  

with cte as
(
select isnull(  case  t1.docType  WHEN 'M' THEN t2.invoiceNo   WHEN 'I' then 
t1.docNo   end  ,'') invoiceNo
from #AccountDoc t1 left join #JobInvoice t2
on t1.id = t2.id  )
select invoiceNo + ',' from cte  For XML PATH ('')