How Concatenate and Get disinct column values 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)  

Select * From #Temp

The expected result

Answer 1

This query seems to work:

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