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


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







All Articles