CODEWITHSUNDEEP
sqlsql-serverdata-cleaning
Kevin
Kevin

Reputation: 421

Combining Two Tables Based on One Column Where Duplicate Values Get Their Own Column

Note: I am using SQL Server Management Studio 2014

I have an issue within SQL that I would like to get sorted out. The overall goal is to export this to excel in a very specific format. I have already tried to export the raw data and do all the configuring in Excel but he formulas I was using were not fast enough to do the task (it was a data cleaning nightmare) so I figured I would try to do it in SQL where I should have tried since the beginning. So here we go...

I have two tables lets call them Table_1 and Table_2.

Table_1 has an ID and a Data Column. There can be many of the same ID's with different data values. (see attached Excel Spreadsheet for the structure)

Table_2 has an ID column that is the foreign key that connects both tables.

I would like to do a SELECT statement to to display the information in the format as shown in EXPECTED RESULT AFTER RUNNING QUERY (again see excel sheet) notice that in the EXPECTED RESULT AFTER RUNNING QUERY there is only one ID for each record. (I am looking for the query to be displayed in SQL Server Management Studio not straight to Excel obviously haha just want to be as clear as possible)This is the sheet that has the expected Result

This is the simplest example I can think of and actually with my real data I need to run this query on multiple columns on Table_1 and display all the results.

I feel like I should keep it as simple as possible and I can build upon it once I have a feel for it. I checked on some other stack answers but I had a few problems reading the answers as they did not make sense to me or they needed to do tasks that were different. Any thoughts or insights would be appreciated!

Thanks in advance!

Upvotes: 0

Views: 86

Answers (1)

Ranjana Ghimire
Ranjana Ghimire

Reputation: 1815

This is exactly what you need:

with m as (
  select 
    ROW_NUMBER() over (partition by id order by data) as Nr, 
    table_1.id, table_1.data 
  from table_1
)
select 
  table_2.id, 
  m1.data as Mail1,
  m2.data as Mail2,
  m3.data as Mail3,
  m4.data as Mail4
from table_2
left join m m1 on (m1.Nr=1 and m1.id=table_2.id)
left join m m2 on (m2.Nr=2 and m2.id=table_2.id)
left join m m3 on (m3.Nr=3 and m3.id=table_2.id)
left join m m4 on (m4.Nr=4 and m4.id=table_2.id)

The result is:

*-------*------------*----------*----------*-------*      
|id     |Mail1       | Mail2     | Mail3   | Mail4 |         
|5290   |cheese      |  napkin   |pie      |  sauce|        
|7893   |cheetos     |  chipoltle|NULL     | NULL  |        
|10170  |fries       |  NULL     | NULL    | NULL  |         
|10179  |burgers     |  NULL     | NULL    | NULL  |         
|42069  |apple sauce |  chilli   | NULL    | NULL  |        
|78913  |baked beans |  NULL     | NULL    | NULL  |  
*-------*------------*----------*----------*-------*

Upvotes: 1

Related Questions