Table normalization, I can’t remove duplicates

Transact-SQL
1

Hello everyone!
I have a table in csv format. Where data is partially missing in different columns, sometimes in the first, sometimes in the second, and so on. Therefore, it is not possible to group by a unique column; data is lost.
ChatGPT can't understand me, and everything it offers doesn't help me.
I'm desperate, please help.

An example of a plate in csv is attached below.
"staff_id","staff_age","staff_first_name","staff_last_name","staff_lang"
"18-31/55",43,Marcellus,Lucas,NULL
"18-31/55",43,NULL,NULL,Guaraní
"18-31/55",NULL,Marcellus,Lucas,Guaraní
"22-98/02",58,Franklin,Paul,
"22-98/02",58,NULL,NULL,Filipino
"22-98/02",NULL,Franklin,Paul,Filipino
"24-21/43",36,Mitchel,Newman,NULL
"24-21/43",36,NULL,NULL,Luxembourgish
"24-21/43",NULL,Mitchel,Newman,Luxembourgish
"24-62/82",17,Hobert,Mckenzie,NULL
"24-62/82",17,NULL,NULL,Tamil
"24-62/82",NULL,Hobert,Mckenzie,Tamil
"33-45/91",58,Malcom,Cunningham,NULL
"33-45/91",58,NULL,NULL,Belarusian
"33-45/91",NULL,Malcom,Cunningham,Belarusian
"34-35/30",37,Louvenia,Black,NULL
"34-35/30",37,NULL,NULL,Nepali
"34-35/30",NULL,Louvenia,Black,Nepali
"49-13/30",43,Charita,Chavez,NULL
"49-13/30",43,NULL,NULL,Gagauz
"49-13/30",NULL,Charita,Chavez,Gagauz
"83-99/74",45,Myron,Ewing,NULL
"83-99/74",45,NULL,NULL,English
"83-99/74",NULL,Myron,Ewing,English
"84-14/62",24,Millard,Leon,NULL
"84-14/62",24,NULL,NULL,Fijian
"84-14/62",NULL,Millard,Leon,Fijian
"91-35/32",55,Cornelius,Goodwin,NULL
"91-35/32",55,NULL,NULL,Czech
"91-35/32",NULL,Cornelius,Goodwin,Czech
NULL,17,Hobert,Mckenzie,Tamil
NULL,24,Millard,Leon,Fijian
NULL,36,Mitchel,Newman,Luxembourgish
NULL,37,Louvenia,Black,Nepali
NULL,43,Charita,Chavez,Gagauz
NULL,43,Marcellus,Lucas,Guaraní
NULL,45,Myron,Ewing,English
NULL,55,Cornelius,Goodwin,Czech
NULL,58,Franklin,Paul,Filipino
NULL,58,Malcom,Cunningham,Belarusian

2

I'm not sure how you're supposed to solve every possible combination of missing data, but here is a strategy for solving some of the cases.

Load the CSV into a staging table.

Identify all of the unique staff_id values and insert them into a table (we'll call it staff) where the staff_id is the primary key.

Next, update the staff table by querying the staging table based on DISTINCT staff_id, staff_age.
Then, update the staff table by querying the staging table based on DISTINCT staff_id, staff_first_name.
Then, update the staff table by querying the staging table based on DISTINCT staff_id, staff_last_name.

Create a new table for staff_language, then insert all of the distinct staff_id, staff_lang combinations into it where staff_id is not null.

Now, at this point you'll only have the rows with the NULL staff_id remaining to match up, and I'll let you take it from there.

4

When I first looked at it, I thought there were multiple languages per staff, but it turns out there is really only one distinct value per staff_id, so you can use a simpler method.

SELECT staff_id
  ,    MAX(staff_age) AS staff_age
  ,    MAX(staff_first_name) AS staff_first_name
  ,    MAX(staff_last_name) AS staff_last_name
  ,    MAX(staff_lang) AS staff_lang
FROM   staging
WHERE  staff_id IS NOT NULL
GROUP BY staff_id;