我已经离开了一段时间,需要帮助,什么可能是一个简单的解决办法,但在这个时候,我卡住了。我有这个select语句,需要为同一个部门合并具有相似元素的行,但是GROUPBY会有一个非常大的select列表,有没有其他类似merge的方法?下面是我正在处理的sql语句以及输出:
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
结果是:
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
这三行需要合并在同一个部门抱歉,如果这是相当初级,但我已经有一段时间没有这种类型的工作,现在需要一个复习。谢谢
1条答案
按热度按时间myzjeezk1#
你想要圆锥聚合。这通过创建
group by
子句,该子句包含除要合并的列以外的所有列,然后用聚合函数(例如MIN()
或者MAX()
:旁注:
我重写了
IIF()
要使用的表达式CASE
(后者是标准sql,而前者是特定于tsql的)单引号应该用于字符串,而不是列标识符(尽管SQLServer允许这样做)
结果集中有多个同名列(
time
),这不是一个好的做法