组合数据库中的行信息

Question

我目前正在处理一个查询，需要为组合一些信息

以下是我到目前为止的查询

SELECT DISTINCT
    s.SiteID,
    s.GroupID,
    sa.StartDateTime,
    u.UserFirstName + SPACE(1) + UserLastName ScanName,
    CONVERT(VARCHAR,sa.StartDateTime,101) ApptStartDate,
    CONVERT(VARCHAR,sa.EndDateTime,101) ApptEndDate,
    CONVERT(VARCHAR(15),CAST(sa.StartDateTime AS TIME),100) ApptStartTime,
    saa.Time ArrivalTime,
    sl.Address,
    st.StateShortCode,
    ct.CityName,
    CASE saa.CheckInCallType 
        WHEN 1 THEN 'Inbound' 
        WHEN 2 THEN 'Outbound' END AS CallCheckInType,
    CASE WHEN saa.CheckInStatus = 1 THEN 'Arrived' 
        WHEN saa.CheckInStatus = 2 THEN 'No Show'
        WHEN saa.CheckInStatus = 3 THEN 'Reschedule'
        WHEN saa.CheckInStatus = 4 THEN 'Provider Reschedule'
        WHEN sa.SiteAppointmentStatusID=998 THEN 'Cancelled'
        ELSE 'Pending' END AS CheckInStatus,
    ISNULL(saa.CheckInComments,'') CheckInComments,
    um.ManagerName,
    u.UserPhone,
    u.UserCell,
    u.UserAddress,
    ci.CityName as ScannerCityName,
    st.StateShortCode as ScannerState
FROM 
    dbo.Sites s
    INNER JOIN dbo.SiteAppointments sa ON s.SiteID = sa.SiteID
    INNER JOIN dbo.SiteAppointmentsScanners sas ON sa.SiteAppointmentID = sas.SiteAppointmentID
    INNER JOIN dbo.Users u ON sas.ScannerUserID = u.UserID
    INNER JOIN dbo.SiteLocation sl ON sa.SiteID = sl.SiteID
    INNER JOIN dbo.States st ON sl.StateID = st.StateID
    INNER JOIN dbo.Cities ct ON sl.CityID = ct.CityID
    LEFT JOIN dbo.SiteAppointmentArrivals saa ON sa.SiteAppointmentID = saa.SiteAppointmentId 
                                              AND saa.ScannerUserID = sas.ScannerUserID
    LEFT JOIN dbo.UserManagers um ON u.UserManagerID=um.ManagerID
    LEFT JOIN dbo.Cities ci ON u.CityID=ci.CityID
    LEFT JOIN dbo.States st2 ON st2.StateID=u.StateID
WHERE 
    ProjectID = 110
    AND 
        (
            (CAST(sa.StartDateTime AS DATE) >= '09/03/2014' AND CAST(sa.StartDateTime AS DATE) <= '09/03/2014')
            OR
            (CAST(sa.EndDateTime AS DATE) >= '09/03/2014' AND CAST(sa.EndDateTime AS DATE) <= '09/03/2014')
        )
    AND ((CAST(saa.Date AS DATE) >= '09/03/2014' AND CAST(saa.Date AS DATE) <= '09/03/2014') OR saa.Date IS NULL)

这些信息都很好，但我想把一些行信息合并成一个信息块。

这里有一个例子：

示例1

Site ID | Group ID | StateDateTime            | ScanName | ApptStartDate | ApptEndDate | ApptStartTime | ArrivalTime             | Address | StateShortCode | CityName | CallCheckInType | CheckInComments | Manager Name | UserPhone | UserCell | UserAddress | ScannerCityName | Scanner State
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
12345   | 67890    | 2014-09-03 09:30:00.000  | Jane Doe | 09/03/2014    | 09/03/2014  | 9:30AM        | 2014-09-03 09:13:01.507 | 123 St  | NY             | New York | Inbound         |                 | Manager1     | 1234567   |          | 321 St      | New York        | NY 
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
54321   | 67890    | 2014-09-03 09:30:00.000  | Jane Doe | 09/03/2014    | 09/03/2014  | 9:30AM        | 2014-09-03 09:13:01.507 | 123 St  | NY             | New York | Inbound         |                 | Manager1     | 1234567   |          | 321 St      | New York        | NY 

GroupID显示许多SiteID（它们都有唯一的编号）实际上被分组到一个GroupID中。我想把这些行合并成一行（而不是显示多个SiteID的多行），只显示一个SiteID（不管是哪一个）

我也得到这样的例子以及

示例2

Site ID | Group ID | StateDateTime            | ScanName | ApptStartDate | ApptEndDate | ApptStartTime | ArrivalTime             | Address | StateShortCode | CityName | CallCheckInType | CheckInComments | Manager Name | UserPhone | UserCell | UserAddress | ScannerCityName | Scanner State
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
09876   | 24680    | 2014-09-03 08:00:00.000  | John Doe | 09/03/2014    | 09/03/2014  | 8:00AM        | NULL                    | 123 St  | NY             | New York | Pending         |                 | Manager1     | 1234567   |          | 321 St      | New York        | NY 
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
09877   | 24680    | 2014-09-03 08:00:00.000  | John Doe | 09/03/2014    | 09/03/2014  | 8:00AM        | 2014-09-03 09:09:48.053 | 123 St  | NY             | New York | Inbound         |                 | Manager1     | 1234567   |          | 321 St      | New York        | NY 

如果用户到达了预约，但没有登录到组内的所有站点（因此我得到了到达时间的空值），但是，如果用户登录到了组内的哪怕一个站点，他们仍然应该被视为到达了整个组内。在这种情况下，我还想"组合"-或删除此处的Null情况，但保留非Null值（在本例中，只有一个站点有日期，但组中可能有多个站点可以登录，但有几个扼杀符为Null）。我知道拥有JOIN而不是LEFT JOIN会有助于解决这个问题。然而，如果一个人没有在一个组中的任何站点中显示为all，我仍然希望获得NULL值。

Answer 1

**根据注释**更新

最便宜的方法是：

SELECT DISTINCT
    max(s.SiteID),
    s.GroupID,
    sa.StartDateTime,
    u.UserFirstName + SPACE(1) + UserLastName ScanName,
    CONVERT(VARCHAR,sa.StartDateTime,101) ApptStartDate,
    CONVERT(VARCHAR,sa.EndDateTime,101) ApptEndDate,
    CONVERT(VARCHAR(15),CAST(sa.StartDateTime AS TIME),100) ApptStartTime,
    saa.Time ArrivalTime,
    sl.Address,
    st.StateShortCode,
    ct.CityName,
    CASE saa.CheckInCallType 
        WHEN 1 THEN 'Inbound' 
        WHEN 2 THEN 'Outbound' END AS CallCheckInType,
    CASE WHEN saa.CheckInStatus = 1 THEN 'Arrived' 
        WHEN saa.CheckInStatus = 2 THEN 'No Show'
        WHEN saa.CheckInStatus = 3 THEN 'Reschedule'
        WHEN saa.CheckInStatus = 4 THEN 'Provider Reschedule'
        WHEN sa.SiteAppointmentStatusID=998 THEN 'Cancelled'
        ELSE 'Pending' END AS CheckInStatus,
    ISNULL(saa.CheckInComments,'') CheckInComments,
    um.ManagerName,
    u.UserPhone,
    u.UserCell,
    u.UserAddress,
    ci.CityName as ScannerCityName,
    st.StateShortCode as ScannerState
FROM 
    dbo.Sites s
    INNER JOIN dbo.SiteAppointments sa ON s.SiteID = sa.SiteID
    INNER JOIN dbo.SiteAppointmentsScanners sas ON sa.SiteAppointmentID = sas.SiteAppointmentID
    INNER JOIN dbo.Users u ON sas.ScannerUserID = u.UserID
    INNER JOIN dbo.SiteLocation sl ON sa.SiteID = sl.SiteID
    INNER JOIN dbo.States st ON sl.StateID = st.StateID
    INNER JOIN dbo.Cities ct ON sl.CityID = ct.CityID
    LEFT JOIN dbo.SiteAppointmentArrivals saa ON sa.SiteAppointmentID = saa.SiteAppointmentId 
                                              AND saa.ScannerUserID = sas.ScannerUserID
    LEFT JOIN dbo.UserManagers um ON u.UserManagerID=um.ManagerID
    LEFT JOIN dbo.Cities ci ON u.CityID=ci.CityID
    LEFT JOIN dbo.States st2 ON st2.StateID=u.StateID
WHERE 
    ProjectID = 110
    AND 
        (
            (CAST(sa.StartDateTime AS DATE) >= '09/03/2014' AND CAST(sa.StartDateTime AS DATE) <= '09/03/2014')
            OR
            (CAST(sa.EndDateTime AS DATE) >= '09/03/2014' AND CAST(sa.EndDateTime AS DATE) <= '09/03/2014')
        )
    AND ((CAST(saa.Date AS DATE) >= '09/03/2014' AND CAST(saa.Date AS DATE) <= '09/03/2014') OR saa.Date IS NULL)
GROUP BY
    2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18, 19 -- short hand for grouping columns together, because there are 20 columns, and only the first one is excluded;