GROUP BY 子句中不允许使用 Sql 服务器 XML 方法

if i can not specify xml data in group by cluase then what other option is available....please guide. thanks

ALTER PROC sp_HourSheetLog
(
    @StartDate VARCHAR(8),
    @EndDate VARCHAR(8)
)
AS

SELECT B.ID
    ,A.RowID
    ,B.EntryDate
    ,B.Name
    ,B.HoursData
    ,B.UpdatedBy
    ,Options=(CASE B.Options
    WHEN 'rdLeave' THEN 'Leave'
    WHEN 'rdsick' THEN 'Sick'
    WHEN 'rdSalvage' THEN 'Salvage'
    WHEN 'rdCSRDuty' THEN 'CSR Duty'
    WHEN 'rdShippingSales' THEN 'Shipping and Sales'
    WHEN 'rdEOL' THEN 'EOL'
    WHEN 'rdTraining' THEN 'Training'
    WHEN 'rdOther' THEN 'Other'
    END)
    ,B.ModDate
        FROM (
        (
            SELECT HourSheetID,(MAX(RowID)+1) as "RowID"
            FROM EditedHourSheetLog l,EditedHourSheet h
            GROUP BY HourSheetID
        ) A
        JOIN
        (
            SELECT h.ID
                ,s.Name
                ,h.EntryDate
                ,h.HoursData
                ,h.Options 
                ,h.UpdatedBy
                ,h.ModDate from EditedHourSheet h
                LEFT JOIN Specialists s
                ON h.SpecialistID=s.SpecialistID
        ) B
    ON A.HourSheetID=B.ID
    )
    WHERE Convert(Varchar,ModDate,112)>=@StartDate AND
    Convert(Varchar,ModDate,112)<=@EndDate


UNION 
(
    Select HourSheetID as ID,RowID,
    XMLData.value('(Log/EntryDate)[1]','datetime') as EntryDate,
    --XMLData.value('(Log/SpecialistID)[1]','int') as SpecialistID,
    s.Name,
    XMLData.value('(Log/HoursData)[1]','decimal(18,2)') as HoursData,
    CAST(XMLData.value('(Log/UpdatedBy)[1]','varchar(max)') AS VARCHAR(MAX)) UpdatedBy,
    Options=(CASE XMLData.value('(Log/Options)[1]','varchar(max)')
    WHEN 'rdLeave' THEN 'Leave'
    WHEN 'rdsick' THEN 'Sick'
    WHEN 'rdSalvage' THEN 'Salvage'
    WHEN 'rdCSRDuty' THEN 'CSR Duty'
    WHEN 'rdShippingSales' THEN 'Shipping and Sales'
    WHEN 'rdEOL' THEN 'EOL'
    WHEN 'rdTraining' THEN 'Training'
    WHEN 'rdOther' THEN 'Other'
    END),
    LogDate as ModDate
    FROM EditedHourSheetLog h
    LEFT JOIN Specialists s
    ON h.XMLData.value('(Log/SpecialistID)[1]','int')=s.SpecialistID
    WHERE Convert(Varchar,LogDate,112)>=@StartDate AND
    Convert(Varchar,LogDate,112)<=@EndDate

)
ORDER BY ID,RowID DESC

--sp_HourSheetLog '20140101','20140326'

推荐答案

您可以使用派生表并在主查询中进行分组.

SELECT T.HourSheetID,
       MAX(T.RowID)+1 as RowID,
       T.EntryDate,
       T.SpecialistID,
       T.HoursData,
       T.UpdatedBy,
       T.Options,
       T.logdate
FROM (
     SELECT HourSheetID,
            RowID,
            XMLData.value('(Log/EntryDate)[1]','datetime') as EntryDate,
            XMLData.value('(Log/SpecialistID)[1]','int') as SpecialistID,
            XMLData.value('(Log/HoursData)[1]','decimal(18,2)') as HoursData,
            XMLData.value('(Log/UpdatedBy)[1]','varchar(max)') as UpdatedBy,
            XMLData.value('(Log/Options)[1]','varchar(max)') as Options,
            logdate
     FROM  dbo.EditedHourSheetLog
     ) AS T
GROUP BY T.HourSheetID,
         T.EntryDate,
         T.SpecialistID,
         T.HoursData,
         T.UpdatedBy,
         T.Options,
         T.logdate