将潜在的重复项分成不同的行

这称为“取消枢纽”。您可以使用

UNPIVOT

CROSS APPLY ... VALUES

我将把查询包装到CTE中，而无需详细查看，并使用将每一行分为两部分

CROSS APPLY

DECLARE@StartDate DATE = '1/1/2017',@EndDate DATE = '3/1/2017';WITHCTEAS(    SELECt          DENSE_RANK() OVER (ORDER BY c.socialSecurityNumber) AS [SSNRanking] ,        ROW_NUMBER() OVER (PARTITION BY c.socialSecurityNumber ORDER BY c.socialSecurityNumber) AS [RowNumb] ,        c.socialSecurityNumber AS [SSN],         c.id AS [CustomerID]  ,         c.firstName AS [FirstName] , c.lastName AS [lastName] ,        c.birthDate [birthdate] ,        c.createDate AS [CreateDate] ,        c2.socialSecurityNumber AS [DupSSN] ,        c2.id AS [DupCustomerID] ,        c2.firstName AS [DupFirstName] ,        c2.lastName AS [DupLastName] ,        c2.birthDate AS [DupBirthDate] ,        c2.createDate AS [DupCreateDate]    FROM dbo.Customers AS [c]        INNER JOIN dbo.Customers AS [c2] ON ( SUBSTRINg(c.socialSecurityNumber,6,4) = SUBSTRINg(c2.socialSecurityNumber,6,4) AND c.birthDate = c2.birthDate AND c.lastName = c2.lastName AND c.id <> c2.id )        LEFT JOIN dbo.CustomerAddresses AS [CA] ON c.id = CA.customerID          LEFT JOIN dbo.Common_Orders AS [co] ON co.customerID = c.id    WHERe        c.customerStatusTypeID <> 'M'        AND C2.customerStatusTypeID <> 'M'        AND c.mergedTo IS NULL        AND c2.mergedTo IS NULL        AND CAST(co.orderDate AS DATE) >= @StartDate        AND CAST(co.orderDate AS DATE) <= @EndDate        AND c.id = 1234439    GROUP BY        c.socialSecurityNumber ,        c.id ,        c.firstName ,        c.lastName ,        c.birthDate ,    c.createDate ,        c2.socialSecurityNumber ,        c2.id ,        c2.firstName ,     c2.lastName ,        c2.birthDate ,        c2.createDate)SELECt    CA.SSNRanking    ,CA.RowNumb    ,CA.SSN    ,CA.CustomerID    ,CA.FirstName    ,CA.lastName    ,CA.birthdate    ,CA.CreateDateFROM    CTE    CROSS APPLY    (        VALUES        (CTE.SSNRanking, CTE.RowNumb, CTE.SSN, CTE.CustomerID, CTE.FirstName, CTE.lastName, CTE.birthdate, CTE.CreateDate),        (CTE.SSNRanking, CTE.RowNumb, CTE.DupSSN, CTE.DupCustomerID, CTE.DupFirstName, CTE.DuplastName, CTE.Dupbirthdate, CTE.DupCreateDate)    ) AS CA(SSNRanking, RowNumb, SSN, CustomerID, FirstName, lastName, birthdate, CreateDate)ORDER BY CAST(CA.SSN AS INT) ASC;

顺便一提，

ROW_NUMBER() OVER (PARTITION BY ColumnA ORDER BY ColumnA)

当您按同一列进行分区和排序时，这没有任何意义。我不确定您想要在那实现什么。