具有相同ID的SQL合并行

Question

因此，我有一个SQL查询，它返回一组结果，如：

ID     Data1      Data2      Data3      Data4
1,     Null,      Null,      Null,      SomeValue
1,     Null,      Null,      SomeValue, Null
1,     Null,      SomeValue, Null,      Null
1,     SomeValue, Null,      Null,      Null
2,     Null,      Null,      Null,      SomeValue
2,     Null,      Null,      SomeValue, Null
2,     Null,      SomeValue, Null,      Null
2,     SomeValue, Null,      Null,      Null

我需要做的是用相同的ID合并所有数据，并生成2行完整的数据，如下所示：

ID       Data1           Data2           Data3           Data4
1,     SomeValue,      SomeValue,      SomeValue,      SomeValue
2,     SomeValue,      SomeValue,      SomeValue,      SomeValue

有人知道这是否可能吗？

我尝试使用GROUP，但是它不允许我在select查询中拥有的其他值。

完整SQL语句：

SELECT      
DISTINCT(D.DocumentNumber) AS [PTW No], 
D.ListCustomField1 AS [Work Order], 
D.ListAssetNumber AS [KKS No],
D.ListWorkToBeDone AS [Description],
D.CurrentStatusName AS [PTW Status],
D.TypeName AS [Section],
dbo.Sem_fn_PTWReport_ProcessData('AreaPicklist', MDP.Value, DCV.ValueString) AS [Area],
dbo.Sem_fn_PTWReport_ProcessData('IssueDate', MDP.Value, D.ID) AS [Actual Start Date], 
dbo.Sem_fn_PTWReport_ProcessData('CloseDate', MDP.Value, D.ID) AS [Actual Finished Date], 
dbo.Sem_fn_PTWReport_ProcessData('Duration', MDP.Value, D.ID) AS [Duration], 
dbo.Sem_fn_PTWReport_ProcessData('Impact', MDP.Value, DCV.ValueString) AS [Potential Non-Availability],
dbo.Sem_fn_PTWReport_ProcessData('DerationWater', MDP.Value, DCV.ValueString) AS [Deration - Water],
dbo.Sem_fn_PTWReport_ProcessData('DerationPower', MDP.Value, DCV.ValueString) AS [Deration - Power],
dbo.Sem_fn_PTWReport_ProcessData('DeclarationNumber', MDP.Value, DCV.ValueString) AS [Availability Decl No/ Rev No]

FROM dbo.Document AS D LEFT OUTER JOIN
     dbo.DocumentControlValues AS DCV ON D.ID = DCV.DocumentId LEFT OUTER JOIN
     dbo.MapperDocumentProperty AS MDP ON DCV.ControlId = MDP.DocumentControlMapperId
WHERE D.Stereotype = '1' 
AND CAST(MDP.Name AS nvarchar(max)) = 'FriendlyName'
AND CAST(MDP.Value AS nvarchar(max)) IN ('AreaP1_Picklist', 'ImpactAvailabilityP3_TextBlock',         'DerationWaterP1_TextBox', 'DerationPowerP1_TextBox', 'DerationDeclarationNoP1_TextBox')  

帮忙欢迎！

干杯，戴夫。

Answer 1

很抱歉给你造成了混乱。这就是我想说的：

SELECT  (D.DocumentNumber) AS [PTW No], 
        MAX(D.ListCustomField1) AS [Work Order], 
        MAX(D.ListAssetNumber) AS [KKS No],
        MAX(D.ListWorkToBeDone) AS [Description],
        MAX(D.CurrentStatusName) AS [PTW Status],
        MAX(D.TypeName) AS [Section],
        MAX(dbo.Sem_fn_PTWReport_ProcessData('AreaPicklist', MDP.Value, DCV.ValueString)) AS [Area],
        MAX(dbo.Sem_fn_PTWReport_ProcessData('IssueDate', MDP.Value, D.ID)) AS [Actual Start Date], 
        MAX(dbo.Sem_fn_PTWReport_ProcessData('CloseDate', MDP.Value, D.ID)) AS [Actual Finished Date], 
        MAX(dbo.Sem_fn_PTWReport_ProcessData('Duration', MDP.Value, D.ID)) AS [Duration], 
        MAX(dbo.Sem_fn_PTWReport_ProcessData('Impact', MDP.Value, DCV.ValueString)) AS [Potential Non-Availability],
        MAX(dbo.Sem_fn_PTWReport_ProcessData('DerationWater', MDP.Value, DCV.ValueString)) AS [Deration - Water],
        MAX(dbo.Sem_fn_PTWReport_ProcessData('DerationPower', MDP.Value, DCV.ValueString)) AS [Deration - Power],
        MAX(dbo.Sem_fn_PTWReport_ProcessData('DeclarationNumber', MDP.Value, DCV.ValueString)) AS [Availability Decl No/ Rev No]
FROM    dbo.Document AS D LEFT OUTER JOIN
        dbo.DocumentControlValues AS DCV ON D.ID = DCV.DocumentId LEFT OUTER JOIN
        dbo.MapperDocumentProperty AS MDP ON DCV.ControlId = MDP.DocumentControlMapperId
WHERE   D.Stereotype = '1' 
        AND CAST(MDP.Name AS nvarchar(max)) = 'FriendlyName'
        AND CAST(MDP.Value AS nvarchar(max)) IN 
        (
            'AreaP1_Picklist',
            'ImpactAvailabilityP3_TextBlock',
            'DerationWaterP1_TextBox',
            'DerationPowerP1_TextBox',
            'DerationDeclarationNoP1_TextBox'
        )
GROUP BY (D.DocumentNumber)