I need to left join a data source to create two tables that match on Account ID. Then I need to display the duplicate match next to a single row if it meets certain criteria (having same first 12 characters in Name, same ZIP, same City). For instance, a duplicate record would be one that has the same ZIP, City, and first 12 letters of the Name but a different Account ID.

Here is an example of what that would look like:

[![enter image description here](https://i.stack.imgur.com/Jb7XF.png)](https://i.stack.imgur.com/Jb7XF.png)

I created 2 instances of the same table and joined them and it gave me what I wanted except it stacked the duplicates on top of one another instead of displaying them next to one another.

Here is an example of what I previously attempted:

```
SELECT TableName1.[Account ID], TableName1.[Account Name], TableName1.[ZIP], TableName1.[City]
  FROM [DataSource] TableName1
  JOIN (SELECT LEFT([Account Name],12) AS [TN2 Account Name], [ZIP] AS [TN2 ZIP], Count(*) AS [Count], [City] AS [TN 2 City]
          FROM [DataSource]
         GROUP BY [Account Name], [ZIP], [City]
        HAVING COUNT(*) >1) TableName2 ON LEFT(TableName1.[Account Name],12) = LEFT(TableName2.[TN2 Account Name],12)
                                      AND TableName1.[ZIP] = TableName2.[ZIP]
                                      AND TableName1.[City] = TableName2.[City] 
ORDER BY TableName1.[Account Name], TableName1.[ZIP], TableName1.[City]

```

It gave me a result like the following:

[![enter image description here](https://i.stack.imgur.com/3mRgf.png)](https://i.stack.imgur.com/3mRgf.png)

Any advice on how I could get duplicate records to show up next to just one match of the Account ID like the first image? First time posting sorry if not enough information.