How I get distinct values in column wise in SQL

Question

Hi,
Ref my Table, I have mentioned the expected result below.

Drop Table #Temp

Create Table #Temp(RowId Int,VillageID Int, VillageName Varchar(10), NoOfMembers Int,GroupID Int,DueDate1 int, DueDate2 int, DueDate3 int, DueDate4 int, DueDate5 int)

Insert into #Temp (RowId,VillageID,VillageName,NoOfMembers,GroupID,DueDate1, DueDate2, DueDate3, DueDate4, DueDate5) Values(1,101,'Salem',20,9001,1,0,3,0,5)

Insert into #Temp (RowId,VillageID,VillageName,NoOfMembers,GroupID,DueDate1, DueDate2, DueDate3, DueDate4, DueDate5) Values(2,101,'Salem',12,9002,4,0,9,7,0)

Insert into #Temp (RowId,VillageID,VillageName,NoOfMembers,GroupID,DueDate1, DueDate2, DueDate3, DueDate4, DueDate5) Values(3,101,'Salem', 4,9003,4,4,5,6,5)

Insert into #Temp (RowId,VillageID,VillageName,NoOfMembers,GroupID,DueDate1, DueDate2, DueDate3, DueDate4, DueDate5) Values(4,102,'KalamNgr', 1,9004,0,0,0,1,2)

Insert into #Temp (RowId,VillageID,VillageName,NoOfMembers,GroupID,DueDate1, DueDate2, DueDate3, DueDate4, DueDate5) Values(5,102,'KalamNgr', 2,9005,0,1,1,1,2)

Insert into #Temp (RowId,VillageID,VillageName,NoOfMembers,GroupID,DueDate1, DueDate2, DueDate3, DueDate4, DueDate5) Values(6,105,'MS.Nagar', 9,9011,20,25,0,0,3)

The expected result is;

• Thanks in advance.

Answer 1

I checked your previous post and you seem to be using SQL Server2016.
So please try:

;with cte  
 as(select   
      row_number() over (order by VillageID) Sr,  
      VillageID, VillageName, sum(NoOfMembers) as NoOfMembers,  
      stuff((select concat(', ', DueDate) from  
          ( select distinct DueDate from (  
              select * from #Temp where VillageID = t.VillageID ) p  
              unpivot (DueDate for d in (DueDate1, DueDate2, DueDate3, DueDate4, DueDate5)) u  
          ) q  
          where DueDate <> 0  
          order by DueDate  
          for xml path('')), 1, 2, '') as DueDates  
  from #Temp t  
  group by VillageID, VillageName)  
  --order by VillageID  
,cte2 as(select *,row_number() over(partition by Sr order by Sr) rr  
from cte   
cross apply string_split(cast(DueDates as varchar),','))  
  
select * from (select Sr,VillageID, VillageName,NoOfMembers,value,rr  
from cte2) t  
pivot (max(value) for rr in ([1],[2],[3],[4],[5],[6],[7])) p  

Output:

Dynamic pivot:

 ;with cte  
  as(select   
       row_number() over (order by VillageID) Sr,  
       VillageID, VillageName, sum(NoOfMembers) as NoOfMembers,  
       stuff((select concat(', ', DueDate) from  
           ( select distinct DueDate from (  
               select * from #Temp where VillageID = t.VillageID ) p  
               unpivot (DueDate for d in (DueDate1, DueDate2, DueDate3, DueDate4, DueDate5)) u  
           ) q  
           where DueDate <> 0  
           order by DueDate  
           for xml path('')), 1, 2, '') as DueDates  
   from #Temp t  
   group by VillageID, VillageName)  
   --order by VillageID  
 ,cte2 as(select *,row_number() over(partition by Sr order by Sr) rr  
 from cte   
 cross apply string_split(cast(DueDates as varchar),','))  
      
 select Sr,VillageID, VillageName,NoOfMembers,value,rr  
 into #test  
 from cte2  
  
 declare @sql nvarchar(max)  
 declare @s nvarchar(max)  
   
 select @s=stuff(( select distinct ',['+CAST(rr as nvarchar)+']'  
 from #test for xml path('')  ), 1, 1, '')  
  
 SET @sql=N'    
  select * from (select Sr,VillageID, VillageName,NoOfMembers,value,rr  
  from #test) t  
 pivot (max(value) for rr in ('+@s+')) as p'  
          
 EXECUTE sp_executesql  @sql  

If you have any question, please feel free to let me know.

Regards
Echo

---

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.