SQL Stored Procedures, Combining rows with derived variables

I’ve been away for sometime and need help with, what is probably a simple solution but at this time I’m stuck. I have this select statement and need to combine rows with like elements for the same department but the group by would have a very large select list, is there another way like MERGE? Here is the sql stmt I’m dealing with along with the output:

SELECT distinct sg.Description as 'Groups', si.Store_Name, a.Store_ID, si.Store_ID1 as 'Store #2', d.Department_ID as 'Dept', a.Vendor_ID,
 IIF(d.Delivery_Day = 1, dbo.DayOfWeekToString(d.Order_Day),null) as 'Sun',
 IIF(d.Delivery_Day = 1, CONCAT(CONVERT(varchar, d.Deadline_Hour) , ':', RIGHT('00' + CONVERT(varchar, d.Deadline_Minute), 2)) ,null ) as 'time',
 IIF(d.Delivery_Day = 2, dbo.DayOfWeekToString(d.Order_Day),null) as 'Mon',
 IIF(d.Delivery_Day = 2, CONCAT(CONVERT(varchar, d.Deadline_Hour) , ':', RIGHT('00' + CONVERT(varchar, d.Deadline_Minute), 2)) ,null ) as 'time',
 IIF(d.Delivery_Day = 3, dbo.DayOfWeekToString(d.Order_Day),null) as 'Tue',
 IIF(d.Delivery_Day = 3, CONCAT(CONVERT(varchar, d.Deadline_Hour) , ':', RIGHT('00' + CONVERT(varchar, d.Deadline_Minute), 2)) ,null ) as 'time',
 IIF(d.Delivery_Day = 4, dbo.DayOfWeekToString(d.Order_Day),null) as  'Wed',
 IIF(d.Delivery_Day = 4, CONCAT(CONVERT(varchar, d.Deadline_Hour) , ':', RIGHT('00' + CONVERT(varchar, d.Deadline_Minute), 2)) ,null ) as 'time',
 IIF(d.Delivery_Day = 5, dbo.DayOfWeekToString(d.Order_Day),null) as  'Thu',
 IIF(d.Delivery_Day = 5, CONCAT(CONVERT(varchar, d.Deadline_Hour) , ':', RIGHT('00' + CONVERT(varchar, d.Deadline_Minute), 2)) ,null ) as 'time',
 IIF(d.Delivery_Day = 6, dbo.DayOfWeekToString(d.Order_Day),null) as  'Fri',
 IIF(d.Delivery_Day = 6, CONCAT(CONVERT(varchar, d.Deadline_Hour) , ':', RIGHT('00' + CONVERT(varchar, d.Deadline_Minute), 2)) ,null ) as 'time',
 IIF(d.Delivery_Day = 7, dbo.DayOfWeekToString(d.Order_Day),null)  as  'Sat',
 IIF(d.Delivery_Day = 7, CONCAT(CONVERT(varchar, d.Deadline_Hour) , ':', RIGHT('00' + CONVERT(varchar, d.Deadline_Minute), 2)),null) as 'time'
 from Delivery_Schedule_Header a  Join Vendor_Info b ON (a.Vendor_ID = b.Vendor_ID) Join Delivery_Schedule_Detail d  on d.UID_Delivery = a.UID 
 Join Store_Info si on si.Store_ID = a.Store_ID Join Store_Group sg  on sg.GroupStore_UID = si.GroupStore_UID 
Where a.Vendor_ID = @Vendor_Id and si.GroupStore_UID = Case when @Group_Id = -1 then si.GroupStore_UID else @Group_Id end

and the results:

Groups  Store_Name  Store_ID    Store #2    Dept    Vendor_ID   Sun time    Mon time    Tue time    Wed time    Thu time    Fri time    Sat time
Balls   Price Chopper 18                18  18  3200    70038000    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Wednesday   11:00   NULL    NULL    NULL    NULL
Balls   Price Chopper 18                18  18  3200    70038000    NULL    NULL    NULL    NULL    Monday  11:00   NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
Balls   Price Chopper 18                18  18  3200    70038000    Saturday    11:00   NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL

These three rows need to be combined under the same department sorry if this is rather elementary but I’ve been out of this type of work for sometime now and need a refresher. Thanks

Answer

You want conitional aggregation. This works by creating a group by clause that contains all columns except those you want to merge, and then surround the conditional expression with an aggregate function (such as MIN() or MAX():

SELECT 
    sg.Description as Groups, 
    si.Store_Name, 
    a.Store_ID, 
    si.Store_ID1 as Store_2, 
    d.Department_ID as Dept, 
    a.Vendor_ID,
    MAX(CASE WHEN d.Delivery_Day = 1 THEN dbo.DayOfWeekToString(d.Order_Day) END) as Sun,
    MAX(CASE 
        WHEN d.Delivery_Day = 1 
        THEN CONCAT(CONVERT(varchar, d.Deadline_Hour) , ':', RIGHT('00' + CONVERT(varchar, d.Deadline_Minute), 2))
    ) as Sun_time,
    ...
FROM Delivery_Schedule_Header a  
JOIN Vendor_Info b               ON a.Vendor_ID = b.Vendor_ID
JOIN Delivery_Schedule_Detail d  ON d.UID_Delivery = a.UID 
JOIN Store_Info si               ON si.Store_ID = a.Store_ID 
JOIN Store_Group sg              ON sg.GroupStore_UID = si.GroupStore_UID 
WHERE 
    a.Vendor_ID = @Vendor_Id 
    AND si.GroupStore_UID = CASE WHEN @Group_Id = -1 THEN si.GroupStore_UID ELSE @Group_Id END
GROUP BY 
    sg.Description as Groups, 
    si.Store_Name, 
    a.Store_ID, 
    si.Store_ID1 as Store_2, 
    d.Department_ID as Dept, 
    a.Vendor_ID

Side notes:

• I rewrote the IIF() expressions to use CASE (the latter is standard SQL, while the former is TSQL-specific)

• single quotes should be used for litteral strings, not for column identifiers (although SQL Server tolerates this)

• you have multiple columns in the resultset that have the same name (time), this is not a good practice