SQL: Combining rows after pivoting-CodePudding

part of an SQL query I'm writing handles a generic metadata table. Thus I'm pivoting the needed elements to fit into the format I need them to be. I'm doing it with "case" and I'm not sure if that's the or a problem, but the result of this subquery comes in this format:

╔════╦═══════╦═══════╗
║ ID ║ DATA1 ║ DATA2 ║
╠════╬═══════╬═══════╣
║  1 ║ 1     ║ (NULL)║
║  1 ║(NULL) ║ 2     ║
║  1 ║(NULL) ║ (NULL)║ 
║  2 ║(NULL) ║ (NULL)║
║  2 ║ 2     ║ (NULL)║
║  3 ║(NULL) ║ (NULL)║
║  4 ║(NULL) ║ (NULL)║
╚════╩═══════╩═══════╝

Not the most beautiful output, but I thought I can fix it with "GROUP BY ID" to streamline the output. It's as following:

╔════╦═══════╦═══════╗
║ ID ║ DATA1 ║ DATA2 ║
╠════╬═══════╬═══════╣
║  1 ║(NULL) ║ (NULL)║ ?????
║  2 ║(NULL) ║   2   ║
║  3 ║(NULL) ║ (NULL)║ 
║  4 ║(NULL) ║ (NULL)║
╚════╩═══════╩═══════╝

Maybe my understanding of GROUP BY is fundamentally wrong, but I did not expect the result for ID 1. Now I'm looking for a way to combine the rows. It should look something like this:

╔════╦═══════╦═══════╗
║ ID ║ DATA1 ║ DATA2 ║
╠════╬═══════╬═══════╣
║  1 ║   1   ║   2   ║ 
║  2 ║(NULL) ║   2   ║
║  3 ║(NULL) ║ (NULL)║ 
║  4 ║(NULL) ║ (NULL)║
╚════╩═══════╩═══════╝

So simply "adding" the rows together is sufficient, since for one ID there will never be two values in a single data column. So it'll always be "number NULL". Is there a way to do this without much hassle? I feel like this shouldn't be to much of a problem, but I can't come up with a solution.

CodePudding user response：

Use an aggregation query:

SELECT ID, MAX(DATA1) AS DATA1, MAX(DATA2) AS DATA2
FROM yourTable
GROUP BY ID
ORDER BY ID;