SQL query table mapping many to one

Question

I have 2 tables.

Table 1 (P_Data)                                      Table 2 (Address)
| Data_Id | First_Name  | Last_Name |                 | ID | Data_Id  | Address_Type | address_data|
|---------|-------------|-----------|                 |----|----------|--------------|-------------|
| 1       | John        | Page      |                 | 1  | 1        | Residential  | address1    | 
| 2       | Bob         | Builder   |                 | 2  | 1        | Bill_To      | address2    |
                                                      | 3  | 2        | Residential  | address3    | 
                                                      | 4  | 2        | Bill_To      | address4    |

I want an output with only 2 fields but it must contain all the addresses (either 1 or many or none) for each id belonging to P_Data table.

Maybe like

| Data_Id | First_Name  | Last_Name |  Address_Type | address_Data |                                                
|---------|-------------|-----------|---------------|--------------|
| 1       | John        | Page      | Residential   | address1     |
|         |             |           | Bill_To       | address2     |              
| 2       | Bob         | Builder   | Residential   | address3     |
|         |             |           | Bill_To       | address4     |

Is it even possible? Or something which won't repeat the Data_Id field but will also give all the Address fields.

Note: The address fields can be none to 10 for one Data_Id. Looking for a generalised query.

EDIT: I tried this

SELECT pd.Data_Id, pd.First_Name, pd.Last_Name, a.Address_Type, a.address_data 
from P_Data as pd 
JOIN Address as a ON pd.Data_Id = a.Data_Id

got this as output

| Data_Id | First_Name  | Last_Name |  Address_Type | address_Data |                                                
|---------|-------------|-----------|---------------|--------------|
| 1       | John        | Page      | Residential   | address1     |
| 1       | John        | Page      | Bill_To       | address2     |              
| 2       | Bob         | Builder   | Residential   | address3     |
| 2       | Bob         | Builder   | Bill_To       | address4     |

Please show us what you have tried and explain where you are stuck. — Dale K, Mar 24 '21 at 06:55
@Zhorov the server is client side, so not very sure about it. But it was setuped in the year 2016 — Satyam Pisal, Mar 24 '21 at 07:13
What you are seeking is a report, not a query result. SQL will repeat values where it is needed so that you can (for example) sort on each column, or extract any row(s) from the result and have values in each column. SQL is NOT a report writer. — Paul Maxwell, Mar 24 '21 at 07:27
Does this answer your question? [How to use GROUP BY to concatenate strings in SQL Server?](https://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server) — Charlieface, Mar 24 '21 at 08:36

Martin Samek · Answer 1 · 2021-03-25T14:37:35.427

In SQL Server 2012 and later you can use lag.

SELECT nullif(pd.Data_Id, lag(pd.Data_Id, 1, null) over (order by pd.Data_Id)) as Data_Id,
  nullif(pd.First_Name, lag(pd.First_Name, 1, null) over (order by pd.Data_Id)) as First_Name,
  nullif(pd.Last_Name, lag(pd.Last_Name, 1, null) over (order by pd.Data_Id)) as Last_Name,
  a.Address_Type, a.address_data 
from P_Data as pd 
JOIN Address as a ON pd.Data_Id = a.Data_Id
ORDER BY pd.Data_Id

score 0 · Answer 2 · answered Mar 24 '21 at 11:56

You can use JOIN, set columns to NULL and carefully sort the results:

select (case when row_number() over (partition by t1.Data_Id order by t2.id) = 1
             then t1.data_id
        end) as data_id,
       (case when row_number() over (partition by t1.Data_Id order by t2.id) = 1
             then t1.First_Name
        end) as First_Name,
       (case when row_number() over (partition by t1.Data_Id order by t2.id) = 1
             then t1.Last_Name
        end) as Last_Name,
       t2.Address_Type, t2.address_Data
from table1 t1 left join
     table2 t2
     on t2.Data_Id = t1.Data_Id
order by t1.Data_Id, t2.id;

Note that this type of transformation is usually better done at the application layer.

SQL query table mapping many to one

2 Answers2