T-SQL Consolidate and merge two rows into one

Question

The goal is to have the Install Address and Dispatch address appear on the same line but I can't figure out a way of doing this. I am doing 2 queries on the same data and doing union all on the results. The address details may be different but also could be the same (eg Install and Dispatch address same).

Select  zSTRI_CertificateNumber, CPS, InstallAdr1, InstallCity, DispAdr1, DispCity, DateSubmitted
From (

    SELECT zSTRI_CertificateNumber,
            'STRI' + CAST(op.ID as Varchar(4)) as CPSref,
            JobRef,
            CAST(CASE
                WHEN notif.Cps = 1 THEN 'CPS'
                END AS varchar(3)) as CPS,
            notif.DateSubmitted,
            nAdr.AddressLine1 as InstallAdr1,
            nAdr.AddressLine2 as InstallAdr2,
            nAdr.City as InstallCity,
            nAdr.PostCode as InstallPostCode,
            '' as DispAdr1,
            '' as DispAdr2,
            '' as DispCity,
            '' as DispPostCode,

            DateWorkCompleted,
            c.CompanyName,
            msrs.UniqueID

    FROM [Notification] notif 
        INNER JOIN NotificationAddress nAdr
            ON notif.ID = nAdr.NotificationID
        INNER JOIN Company c
            ON c.CompanyID = notif.CompanyID
        INNER JOIN NotificationMeasures msrs
            ON notif.ID = msrs.NotificationID
        INNER JOIN Operative op
            ON op.ID = NotifyingOperativeID
    WHERE  notif.DispatchMethodEmail = 0
            AND nAdr.InstallAddress = 1
             AND notif.ID = 5411
        UNION ALL


    SELECT zSTRI_CertificateNumber,
            'STRI' + CAST(op.ID as Varchar(4)) as CPSref,
            JobRef,
            CAST(CASE
                WHEN notif.Cps = 1 THEN 'CPS'
                END AS varchar(3)) as CPS,
            notif.DateSubmitted,
            '' as InstallAdr1,
            '' as InstallAdr2,
            '' as InstallCity,
            '' as InstallPostCode,

            nAdr.AddressLine1 as DispAdr1,
            nAdr.AddressLine2 as DispAdr2,
            nAdr.City as DispCity,
            nAdr.PostCode as DispPostCode,
            DateWorkCompleted,
            c.CompanyName,
            msrs.UniqueID

    FROM [Notification] notif 
        INNER JOIN NotificationAddress nAdr
            ON notif.ID = nAdr.NotificationID
        INNER JOIN Company c
            ON c.CompanyID = notif.CompanyID
        INNER JOIN NotificationMeasures msrs
            ON notif.ID = msrs.NotificationID
        INNER JOIN Operative op
            ON op.ID = NotifyingOperativeID
    WHERE
            notif.DispatchMethodEmail = 0
            AND nAdr.DispatchAddress = 1 
            AND notif.ID = 5411
) as SubGroup
Group by  zSTRI_CertificateNumber, CPS, InstallAdr1, InstallCity, DispAdr1, DispCity, DateSubmitted

Answer 1

Here is a summarised version of the query which does the job (based on the accepted answer)

Select NotifId, MAX(InstallAddress1) InstallAddress1, 
        MAX(InstallAddress2) InstallAddress2,
        MAX(InstallCity) InstallCity,
        MAX(InstallPostCode) InstallPostCode,
        MAX(DispatchAddress1) DispatchAddress1,
        MAX(DispatchAddress2) DispatchAddress2,
        MAX(DispatchCity) DispatchCity,
        MAX(DispatchPostCode) DispatchPostCode
    FROM (
        select  X.NotificationID as NotifId,
            X.AddressLine1 as 'InstallAddress1',
            X.AddressLine2 as 'InstallAddress2',
            X.City as 'InstallCity',
            X.Postcode as 'InstallPostCode',
            null as 'DispatchAddress1',
            null as 'DispatchAddress2',
            null as 'DispatchCity',
            null as 'DispatchPostCode'
        from NotificationAddress X
        Where X.InstallAddress = 1

        UNION
        Select Y.NotificationID as NotifId,
            null as 'InstallAddress1',
            null as 'InstallAddress2',
            null as 'InstallCity',
            null as 'InstallPostCode',
            Y.AddressLine1 as 'DispatchAddress1',
            Y.AddressLine2 as 'DispatchAddress2',
            Y.City as 'DispatchCity',
            Y.Postcode as 'DispatchPostCode'
            from NotificationAddress Y
        where   Y.DispatchAddress = 1
 ) as b
    GROUP BY  NotifId

Answer 2

It sounds like you just need to join on the same NotificationAddress table twice within a single query, using different join criteria.

e.g.

select A.id, X.value as 'xValue', Y.value as 'yValue'
from IdTable A
inner join ValueTable X
    on A.id=X.id
inner join ValueTable Y      -- same table as "X"
    on A.id=Y.id
where X.type = 'X'
and   Y.type = 'Y'           -- but different join criteria

Answer 3

may be you are code is so huge as per my assumption and Use the MAX values for some NULL columns and Remove them in Group BY

SELECT 
 zSTRI_CertificateNumber, 
 CPS, 
 MAX(InstallAdr1) InstallAdr1, 
InstallCity, 
MAX(DispAdr1)DispAdr1,
 MAX(DispCity)DispCity, 
DateSubmitted

            FROM (
     SELECT zSTRI_CertificateNumber,
                'STRI' + CAST(op.ID as Varchar(4)) as CPSref,
                JobRef,
                CAST(CASE
                    WHEN notif.Cps = 1 THEN 'CPS'
                    END AS varchar(3)) as CPS,
                notif.DateSubmitted,
                nAdr.AddressLine1 as InstallAdr1,
                nAdr.AddressLine2 as InstallAdr2,
                nAdr.City as InstallCity,
                nAdr.PostCode as InstallPostCode,
                '' as DispAdr1,
                '' as DispAdr2,
                '' as DispCity,
                '' as DispPostCode,

                DateWorkCompleted,
                c.CompanyName,
                msrs.UniqueID

        FROM [Notification] notif 
            INNER JOIN NotificationAddress nAdr
                ON notif.ID = nAdr.NotificationID
            INNER JOIN Company c
                ON c.CompanyID = notif.CompanyID
            INNER JOIN NotificationMeasures msrs
                ON notif.ID = msrs.NotificationID
            INNER JOIN Operative op
                ON op.ID = NotifyingOperativeID
        WHERE  notif.DispatchMethodEmail = 0
                AND nAdr.InstallAddress = 1
                 AND notif.ID = 5411
            UNION ALL


        SELECT zSTRI_CertificateNumber,
                'STRI' + CAST(op.ID as Varchar(4)) as CPSref,
                JobRef,
                CAST(CASE
                    WHEN notif.Cps = 1 THEN 'CPS'
                    END AS varchar(3)) as CPS,
                notif.DateSubmitted,
                '' as InstallAdr1,
                '' as InstallAdr2,
                '' as InstallCity,
                '' as InstallPostCode,

                nAdr.AddressLine1 as DispAdr1,
                nAdr.AddressLine2 as DispAdr2,
                nAdr.City as DispCity,
                nAdr.PostCode as DispPostCode,
                DateWorkCompleted,
                c.CompanyName,
                msrs.UniqueID

        FROM [Notification] notif 
            INNER JOIN NotificationAddress nAdr
                ON notif.ID = nAdr.NotificationID
            INNER JOIN Company c
                ON c.CompanyID = notif.CompanyID
            INNER JOIN NotificationMeasures msrs
                ON notif.ID = msrs.NotificationID
            INNER JOIN Operative op
                ON op.ID = NotifyingOperativeID
        WHERE
                notif.DispatchMethodEmail = 0
                AND nAdr.DispatchAddress = 1 
                AND notif.ID = 5411

            )As Subgroup
            GROUP BY zSTRI_CertificateNumber, CPS, InstallCity, DateSubmitted