We have multiple same id's and some of them have values and some of them are null, we wanted to copy the values where it is null.
Current scenario
| id | value |
|---|---|
| 1 | null |
| 1 | null |
| 1 | null |
| 1 | ABC |
| 1 | null |
Expected
| id | value |
|---|---|
| 1 | ABC |
| 1 | ABC |
| 1 | ABC |
| 1 | ABC |
| 1 | ABC |
Asked
Active
Viewed 46 times
-2
Ishwar
- 1
-
4What would be the result if another id = 1 row had DEF as value? – jarlh Jan 23 '23 at 13:09
-
Please provide enough code so others can better understand or reproduce the problem. – Community Jan 23 '23 at 16:08
1 Answers
1
One way will be to use aggregation function to get the value for the particular group:
SELECT id
,COALESCE(value, MAX(value) OVER (PARTITION BY id)) As "value"
FROM table
Of course, you can use other aggregation function like MIN (to get the smallest of available values) or STRING_AGG to get a list with the values.
gotqn
- 42,737
- 46
- 157
- 243