CODEWITHSUNDEEP
sqlgroup-by
David C
David C

Reputation: 694

SQL Merge rows with same ID

So I have a SQL query that returns a set of results like:

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

And what I need to do is merge all with the same ID and make 2 rows of complete data like so:

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

Anyone know if this is possible?

I tried using GROUP BY but it doesn't permit the rest of the values that I have in the select query.

Full SQL statement:

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')

Help welcome!

Cheers, Dave.

Upvotes: 0

Views: 2309

Answers (1)

programmer43229
programmer43229

Reputation: 386

Sorry for the confusion. This is what I was trying to suggest:

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)

Upvotes: 1

Related Questions