How to ignore columns in select statement if it has Zero value in SQL

Question

Hi,

How to ignore a column in the select statement if it has Zero value in SQL(2016)

This is my table,   
  
 Drop Table #Temp   
  
 Create Table #Temp(RowId Int,DueDate1 int, DueDate2 int, DueDate3 int, DueDate4 int, DueDate5 int)  
 Insert into #Temp (RowId, DueDate1, DueDate2, DueDate3, DueDate4, DueDate5) Values(1,1,0,3,0,5)  
 Insert into #Temp (RowId,DueDate1, DueDate2, DueDate3, DueDate4, DueDate5) Values(2,0,0,0,4,5)  
 Insert into #Temp (RowId,DueDate1, DueDate2, DueDate3, DueDate4, DueDate5) Values(3,1,0,0,4,0)  
 Insert into #Temp (RowId,DueDate1, DueDate2, DueDate3, DueDate4, DueDate5) Values(4,0,0,3,0,0)  
      
Select * from #Temp   

Expected result:

Thanks in advance.

Answer 1

Try the solutions that can be found here: https://learn.microsoft.com/en-us/answers/questions/80464/. For example:

;  
with Q4 as  
(  
    select RowId, DueDate1, DueDate2, DueDate3, DueDate4, DueDate5  
    from #Temp where DueDate4 <> 0  
    union all  
    select RowId, DueDate1, DueDate2, DueDate3, DueDate5, 0  
    from #Temp where DueDate4 = 0  
),  
Q3 as  
(  
    select RowId, DueDate1, DueDate2, DueDate3, DueDate4, DueDate5  
    from Q4 where DueDate3 <> 0  
    union all  
    select RowId, DueDate1, DueDate2, DueDate4, DueDate5, 0  
    from Q4 where DueDate3 = 0  
),  
Q2 as  
(  
    select RowId, DueDate1, DueDate2, DueDate3, DueDate4, DueDate5  
    from Q3 where DueDate2 <> 0  
    union all  
    select RowId, DueDate1, DueDate3, DueDate4, DueDate5, 0  
    from Q3 where DueDate2 = 0  
),  
Q1 as  
(  
    select RowId, DueDate1, DueDate2, DueDate3, DueDate4, DueDate5  
    from Q2 where DueDate1 <> 0  
    union all  
    select RowId, DueDate2, DueDate3, DueDate4, DueDate5, 0  
    from Q2 where DueDate1 = 0  
)  
select   
    RowId,  
    format(DueDate1, '#') DD1,  
    format(DueDate2, '#') DD2,  
    format(DueDate3, '#') DD3,  
    format(DueDate4, '#') DD4,  
    format(DueDate5, '#') DD5  
from Q1  
order by RowId  
  

Answer 2

Here is a variation of my solution from the link that Viorel provided.

SQL

-- DDL and sample data population, start
DECLARE  @tbl TABLE (RowId Int,DueDate1 int, DueDate2 int, DueDate3 int, DueDate4 int, DueDate5 int);
INSERT INTO @tbl (RowId, DueDate1, DueDate2, DueDate3, DueDate4, DueDate5) VALUES
(1,1,0,3,0,5),
(2,0,0,0,4,5),
(3,1,0,0,4,0),
(4,0,0,3,0,0);
-- DDL and sample data population, end

SELECT RowId
   , x.value('(*[./text()!="0"][1]/text())[1]','INT') AS t1
   , x.value('(*[./text()!="0"][2]/text())[1]','INT') AS t2
   , x.value('(*[./text()!="0"][3]/text())[1]','INT') AS t3
   , x.value('(*[./text()!="0"][4]/text())[1]','INT') AS t4
   , x.value('(*[./text()!="0"][5]/text())[1]','INT') AS t5
FROM @tbl
    CROSS APPLY (
        SELECT DueDate1, DueDate2, DueDate3, DueDate4, DueDate5 
        FOR XML PATH(''), type) AS t(x);

Answer 3

Hi @Aypn CNN ,

Please also refer below using UNPIVOT and PIVOT:

;with cte as (  
SELECT RowId, DD, value    
FROM  (select * FROM #Temp) p    
UNPIVOT (value FOR DD IN (DueDate1, DueDate2, DueDate3,DueDate4,DueDate5)    
)AS unpvt)  
,cte1 as (  
select *,ROW_NUMBER() over (partition by rowid order by DD) rn from cte where value <>0)  
select * from   
(select RowId,value,'DD'+cast(rn as char(1)) DD from cte1 ) s  
pivot (max(value) for DD in ([DD1], [DD2], [DD3],[DD4],[DD5]))p  

Output:

RowId	DD1	DD2	DD3	DD4	DD5  
1	1	3	5	NULL	NULL  
2	4	5	NULL	NULL	NULL  
3	1	4	NULL	NULL	NULL  
4	3	NULL	NULL	NULL	NULL  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.