CODEWITHSUNDEEP
sqlsql-servergroup-by
Shannon Rogers
Shannon Rogers

Reputation: 45

Merging duplicate results in one column into a single row - while also removing nulls in all other columns

The results from a script I have created contains some information that I would like to consolidate into a single row for each unique ID.

Example of results.

ID|File1|File2|File3|File4
1 |1.txt|NULL |3.txt|4.txt
1 |NULL |2.txt|NULL |4.txt
2 |NULL |2.txt|NULL |4.txt
2 |1.txt|NULL |3.txt|4.txt

I am trying to script the above table to include a unique ID, along with removing all of the NULL values.

The results I am trying to achieve are below.

ID|File1|File2|File3|File4 
1 |1.txt|2.txt|3.txt|4.txt 
2 |1.txt|2.txt|3.txt|4.txt

I need to basically group Files 1,2,3, and 4 for the unique ID they belong to, while also removing any null values.

I am out of ideas, and I honestly don't know the best words to describe my issue, so that Google pulls back some matching results.

If all else fails, I can always paste the results in excel and use Vlookup to get my results, but I am trying to script the results I need.

Thanks in advance

Upvotes: 0

Views: 32

Answers (1)

Squirrel
Squirrel

Reputation: 24763

GROUP BY ID with MAX() will do the job

SELECT  ID, max(file1), max(file2), max(file3), max(file4)
FROM    table
GROUP BY ID

Upvotes: 3

Related Questions