SQL- If the Row is duplicated make it as a new column nested of duplicate it

Question

I have a three table one for applications to get the ID and one for applicantInfo and one for address i need to create a view that will show me the data of each column but with a condition that one row for each application and the addresses need it be beside of themselves. i make this Select Query:

Select dbo.Application.ApplicationID, dbo.ApplicantInfo.FirstName,dbo.ApplicantInfo.LastName, dbo.Address.AddressID,dbo.Address.City from dbo.Application LEFT JOIN dbo.ApplicantInfo on dbo.Application.ApplicationID = dbo.ApplicantInfo.ApplicationID LEFT JOIN dbo.Address ON dbo.Application.ApplicationID = dbo.Address.ApplicationID the output getting like this: <

<table style="width:100%">
  <tr>
    <th>ApplicationID</th>
    <th>FirstName</th> 
    <th>LastName</th>
    <th>AddressID</th> 
    <th>City</th>
  </tr>
  <tr>
    <td>1</td>
    <td>John</td> 
    <td>Zak</td>
    <th>1</th> 
    <th>C1</th>
  </tr>
  <tr>
    <td>1</td>
    <td>John</td> 
    <td>Zak</td>
    <th>2</th> 
    <th>C2</th>
  </tr>
    <tr>
    <td>1</td>
    <td>John</td> 
    <td>Zak</td>
    <th>3</th> 
    <th>C3</th>
  </tr>
    <tr>
    <td>1</td>
    <td>John</td> 
    <td>Zak</td>
    <th>4</th> 
    <th>C4</th>
  </tr>
</table>

but i need the output like this :

<table style="width:100%">
  <tr>
    <th>ApplicationID</th>
    <th>FirstName</th> 
    <th>LastName</th>
    <th>AddressID1</th> 
    <th>City1</th>
    <th>AddressID2</th>
    <th>City2</th>
    <th>AddressID3</th>
    <th>City3</th>
    <th>AddressID4</th>
    <th>City4</th>
  </tr>
  <tr>
    <td>1</td>
    <td>John</td> 
    <td>Zak</td>
    <td>A1</td>
    <td>C1</td>
    <td>A2</td> 
    <td>C2</td>
    <td>A3</td> 
    <td>C3</td>
    <td>4</td> 
    <td>C4</td>
  </tr>
</table>

Possible duplicate of [SQL Server dynamic PIVOT query?](http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Serg, Dec 22 '16 at 12:42
Thank you Serg for your reply but it need it with my Query because i have Left joins — Ahmed Hossam Alhansy, Dec 22 '16 at 12:49

John Cappelletti · Accepted Answer · 2016-12-22T14:48:22.453

If you don't need DYNAMIC, a simple conditional aggregation will do the trick

Select ApplicationID
      ,FirstName
      ,LastName
      ,AddressID1 = max(case when AddressID=1 then concat('A',AddressID) else '' end)
      ,City1      = max(case when AddressID=1 then City else '' end)
      ,AddressID2 = max(case when AddressID=2 then concat('A',AddressID) else '' end)
      ,City2      = max(case when AddressID=2 then City else '' end)
      ,AddressID3 = max(case when AddressID=3 then concat('A',AddressID) else '' end)
      ,City3      = max(case when AddressID=3 then City else '' end)
      -- ... Expand as necessary
 From (
       -- Your Complicated Query
      ) A
 Group By 
       ApplicationID
      ,FirstName
      ,LastName

Returns

EDIT for Updated Info

Notice the RN = Row_Number() line

Select ApplicationID
      ,FirstName
      ,LastName
      ,AddressID1 = max(case when RN=1 then concat('A',AddressID) else '' end)
      ,City1      = max(case when RN=1 then City                  else '' end)
      ,AddressID2 = max(case when RN=2 then concat('A',AddressID) else '' end)
      ,City2      = max(case when RN=2 then City                  else '' end)
      ,AddressID3 = max(case when RN=3 then concat('A',AddressID) else '' end)
      ,City3      = max(case when RN=3 then City                  else '' end)
      ,AddressID4 = max(case when RN=4 then concat('A',AddressID) else '' end)
      ,City4      = max(case when RN=4 then City                  else '' end)
      ,AddressID5 = max(case when RN=5 then concat('A',AddressID) else '' end)
      ,City5      = max(case when RN=5 then City                  else '' end)
      ,AddressID6 = max(case when RN=6 then concat('A',AddressID) else '' end)
      ,City6      = max(case when RN=6 then City                  else '' end)
      ,AddressID7 = max(case when RN=7 then concat('A',AddressID) else '' end)
      ,City7      = max(case when RN=7 then City                  else '' end)
      ,AddressID8 = max(case when RN=8 then concat('A',AddressID) else '' end)
      ,City8      = max(case when RN=8 then City                  else '' end)
      ,AddressID9 = max(case when RN=9 then concat('A',AddressID) else '' end)
      ,City9      = max(case when RN=9 then City                  else '' end)
 From (
        Select dbo.Application.ApplicationID
             , dbo.ApplicantInfo.FirstName
             , dbo.ApplicantInfo.LastName
             , dbo.Address.AddressID
             , dbo.Address.City 
             , RN = Row_Number() over (Partition By ApplicationID,FirstName,LastName Order By AddressID) 
         from dbo.Application 
         LEFT JOIN  dbo.ApplicantInfo on dbo.Application.ApplicationID = dbo.ApplicantInfo.ApplicationID 
         LEFT JOIN  dbo.Address ON dbo.Application.ApplicationID = dbo.Address.ApplicationID
      ) A
 Group By 
       ApplicationID
      ,FirstName
      ,LastName

Returns

the data isn't constant as A1 or C1 it's just a name of cities in the database which selected from the Address table — Ahmed Hossam Alhansy, Dec 22 '16 at 13:12
@AhmedHossamAlhansy Date? I don't see any date in your sample data — John Cappelletti, Dec 22 '16 at 13:16
@AhmedHossamAlhansy Not sure I completely understand. Is the AddressID a sequence number by account or is it a fk? — John Cappelletti, Dec 22 '16 at 13:23
@AhmedHossamAlhansy Perhaps a more representative sample data would help — John Cappelletti, Dec 22 '16 at 13:24
look Sr. the each application has a id and w lot of addresses id, with a cities name just saved in the database not must be a sequence — Ahmed Hossam Alhansy, Dec 22 '16 at 13:27
@AhmedHossamAlhansy OK, the image helps. So if I understand you could have you have numerous addresses (more than say 4). This would then require dynamic and row_number()... give me a moment — John Cappelletti, Dec 22 '16 at 13:32
@AhmedHossamAlhansy Just add the Row_Number() line to your "comlipcated query" — John Cappelletti, Dec 22 '16 at 14:24

score 0 · Answer 2 · answered Dec 22 '16 at 13:00

If you only ever want 4 address rows then you can avoid using pivot by simply joining to the address table 4 times. You'll need some way to identify which address records correspond to 1,2,3 & 4. Or you can create this with a row_number(). EG

with address_numbered as
(select *, row_number() over (partition by applicationId order by addressid) as row_num from address)
Select dbo.Application.ApplicationID, dbo.ApplicantInfo.FirstName,dbo.ApplicantInfo.LastName,
a1.AddressID,a1.City ,
a2.AddressID as AddressID2,a2.City as City 
from dbo.Application LEFT JOIN 
dbo.ApplicantInfo on dbo.Application.ApplicationID = dbo.ApplicantInfo.ApplicationID 
LEFT JOIN 
address_numbered a1 ON dbo.Application.ApplicationID = a1.ApplicationID
and a1.row_num = 1
LEFT JOIN 
address_numbered a2 ON dbo.Application.ApplicationID = dbo.Address.ApplicationID
and a2.row_num = 2

I got bored after 2 joins but you get the idea...

SQL- If the Row is duplicated make it as a new column nested of duplicate it

2 Answers2