Remove null values and merge sql server 2008 r2
I have a table (TestTable) as follows
PK | COL1 | COL2 | COL3
1 | 3 | NULL | NULL
2 | 3 | 43 | 1.5
3 | 4 | NULL | NULL
4 | 4 | NULL | NULL
5 | 4 | 48 | 10.5
6 | NULL | NULL | NULL
7 | NULL | NULL | NULL
8 | NULL | NULL | NULL
9 | 5 | NULL | NULL
10 | 5 | NULL | NULL
11 | 5 | 55 | 95
I would like to get the following result
PK | COL1 | COL2 | COL3
1 | 3 | 43 | 1.5
2 | 4 | 48 | 10.5
3 | 5 | 55 | 95
0
source to share
2 answers
You can do this, but it won't give you the serial number for PK
:
SELECT
PK,
MAX(Col1) AS Col1,
MAX(Col2) AS Col2,
MAX(Col3) AS Col3
FROM TestTable
WHERE Col1 IS NOT NULL
AND Col2 IS NOT NULL
AND COL3 IS NOT NULL
GROUP BY PK;
| PK | COL1 | COL2 | COL3 |
|----|------|------|------|
| 2 | 3 | 43 | 1.5 |
| 5 | 4 | 48 | 10.5 |
| 11 | 5 | 55 | 95 |
If you want to generate a number for the pk column, you can do this:
WITH CTE
AS
(
SELECT
PK,
MAX(Col1) AS Col1,
MAX(Col2) AS Col2,
MAX(Col3) AS Col3
FROM TestTable
WHERE Col1 IS NOT NULL
AND Col2 IS NOT NULL
AND COL3 IS NOT NULL
GROUP BY PK
), Ranked
AS
(
SELECT *, ROW_NUMBER() OVER(ORDER BY PK) AS RN
FROM CTE;
)
SELECT RN AS PK, Col1, COL2, COL3 FROM Ranked
This will give you:
| PK | COL1 | COL2 | COL3 |
|----|------|------|------|
| 1 | 3 | 43 | 1.5 |
| 2 | 4 | 48 | 10.5 |
| 3 | 5 | 55 | 95 |
+1
source to share
This can be done in two steps:
1st step: get rid of extra lines:
delete from testTable
where Col1 is null
or Col2 is null
or Col3 is null
2nd step: set PK correction values using CTE (update test table):
;with sanitizeCTE
as(
select ROW_NUMBER() over (order by PK) as PK,
Col1, Col2, Col3
from testTable
)
update t
set t.PK = CTE.PK
from testTable t
join sanitizeCTE cte
on t.Col1 = cte.Col1
and t.Col2 = cte.Col2
and t.Col3 = cte.Col3
Tested here: http://sqlfiddle.com/#!3/91e86/1
0
source to share