SQLSERVER中合并多条记录为一条

     根据业务需要,在水晶报表中把数据库的多条记录显示为整合为一条。即把同一ID号的出件接收时间整合为一条，且标识出是第几条的接收时间。

     数据库中的数据样式如下：
          SAID      ReceivedDate
          000104   2006-03-01
          000104   2006-03-04
          000104   2006-03-07
          000104   2006-03-08
          000105   2006-08-09

-----------水晶报表中显示的样式为：
         SAID       1st Received Date     2nd Received Date     3rd Received Date   4th Received Date
         000104         2006-03-01             2006-03-04                2006-03-07          2006-03-08
         000105         2006-08-09
---------------------SQL语句为：

select [SA].[SAID]
            ,MAX(case when xh = 1 then SentDate  end) as EmailNotice
            ,MAX(case when xh = 2 then SentDate  end) as FirstReminder
            ,MAX(case when xh = 3 then SentDate  end) as SecondReminder
            ,MAX(case when xh = 4 then [SA].[LastReminderDate]  end) as AlertToQCS 
            ,[SA].[VendorName],[SA].[FactoryName] 
from(select *,xh=(select xh = count(*) from [#a] where a.ReferenceID = ReferenceID and a.xid>=xid) from [#a] a) b , [SA]
WHERE
            [SA].[SAID]=b.ReferenceID AND
            (ISNULL(@CustomerID,'')=''OR [SA].CustomerID=@CustomerID) AND
            (ISNULL(@VendorID,'')='' OR [SA].VendorID=@VendorID) AND
            (ISNULL(@FactoryID,'')='' OR [SA].FactoryID=@FactoryID) AND 
            (ISNULL(@BeginDate,'1999-1-1')='1999-1-1' OR [SA].FirstReminderDate>= @BeginDate) AND
            (ISNULL(@EndDate,'2999-1-1')='2999-1-1' OR [SA].FirstReminderDate<=@EndDate) AND
            (ISNULL(@SAID1,'')='' OR CAST([SA].SAID AS int) >=CAST(@SAID1 AS INT)) AND 
            (ISNULL(@SAID2,'')='' OR CAST(SA.SAID AS INT)<=CAST(@SAID2 AS INT)) AND 
            [SA].Confirmed=1 AND 
            [SA].Booked=0 AND
            SA.Deleted=0
            AND SA.CompanyID=@CompanyID AND 
            SA.DepartmentID=@DepartmentID AND SA.[DivisionID]=@DivisionID
GROUP BY ReferenceID,[SA].[VendorName],[SA].[FactoryName],[SA].[SAID]
drop table #a