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 a result as follows

PK  |  COL1  |  COL2  |  COL3  
1   |  3     |  43    |  1.5    
2   |  4     |  48    |  10.5    
3   |  5     |  55    |  95  

回答1:

You can do this, But it won't give you a serial number for the 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 rownumber for the column pk, 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

• SQL Fiddle Demo

This will give you:

| PK | COL1 | COL2 | COL3 |
|----|------|------|------|
|  1 |    3 |   43 |  1.5 |
|  2 |    4 |   48 | 10.5 |
|  3 |    5 |   55 |   95 |

回答2:

This can be obtained in two steps like so:

1st step: Get rid of unnecessary rows:

delete from testTable 
where Col1 is null
    or Col2 is null
    or Col3 is null

2nd step: Set the correck PK values using a 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