sql-server-2008 - 在 SQL Server 中删除数据中的空值

Question

查询结果为

ChannelType |  HA BM  | AV BM      |  COUNTRY HEAD
-----------------------------------------------
  RS        |  NULL   |  NULL      |    abc
  RS        |  NULL   |  NULL      |    xyz
  RS        |  NULL   | prasanta.p |    NULL
  RS        |  NULL   | v.sanjay   |    NULL
  RS        | utpal.c | NULL       |    NULL
  RS        | vipul.k | NULL       |    NULL

我希望最终结果是:

ChannelType |  HA BM  | AV BM      |  COUNTRY HEAD
-----------------------------------------------
RS          | utpal.c | prasanta.p |    abc
RS          | vipul.k | v.sanjay   |    xyz

我想删除记录中的空值。

提前致谢

Answer 1

在 SQL Server 2008 中，您可以使用 ROW_NUMBER() 获取值的顺序，然后进行自连接。像这样。

数据

DECLARE @table TABLE
(
    ChannelType CHAR(2),
    [HA BM] VARCHAR(10),
    [AV BM] VARCHAR(10),
    [COUNTRY HEAD] CHAR(3)
)
INSERT INTO @table VALUES
('RS',NULL,NULL,'abc'),
('RS',NULL,NULL,'xyz'),
('RS',NULL,'prasanta.p',NULL),
('RS',NULL,'v.sanjay',NULL),
('RS','utpal.c',NULL, NULL),
('RS','vipul.k',NULL, NULL)

查询

;WITH CTE as 
(
SELECT ChannelType,[HA BM],[AV BM],[COUNTRY HEAD],
ROW_NUMBER()OVER(ORDER BY CASE WHEN [HA BM] IS NULL THEN 2 ELSE 1 END ASC) h_row,
ROW_NUMBER()OVER(ORDER BY CASE WHEN [AV BM] IS NULL THEN 2 ELSE 1 END ASC) a_row,
ROW_NUMBER()OVER(ORDER BY CASE WHEN [COUNTRY HEAD] IS NULL THEN 2 ELSE 1 END ASC) c_row
FROM @table
)
SELECT C1.ChannelType,C1.[HA BM],C2.[AV BM],C3.[COUNTRY HEAD]
FROM CTE C1
LEFT JOIN CTE C2 ON C1.h_row = C2.a_row AND C1.ChannelType = C2.ChannelType AND C2.a_row <> 0
LEFT JOIN CTE C3 ON C1.h_row = C3.c_row AND C1.ChannelType = C3.ChannelType AND C3.c_row <> 0
WHERE C1.[HA BM] IS NOT NULL AND C2.[AV BM] IS NOT NULL AND C3.[COUNTRY HEAD] IS NOT NULL

SQL Server 2012及以后的版本，可以使用LEAD和COUNT() OVER()。像这样

;WITH CTE as 
(
SELECT ChannelType,
LEAD([HA BM])OVER(ORDER BY [HA BM] ASC) [HA BM],
LEAD([AV BM])OVER(ORDER BY [AV BM] ASC) [AV BM],
LEAD([COUNTRY HEAD])OVER(ORDER BY [COUNTRY HEAD] ASC) [COUNTRY HEAD],
COUNT([HA BM])OVER(ORDER BY [HA BM] ASC)  +
COUNT([AV BM])OVER(ORDER BY [AV BM] ASC) +
COUNT([COUNTRY HEAD])OVER(ORDER BY [COUNTRY HEAD] ASC) c_row
FROM @table
)
SELECT ChannelType,MAX([HA BM]) [HA BM],MAX([AV BM]) [AV BM],MAX([COUNTRY HEAD]) [COUNTRY HEAD]
FROM CTE
GROUP BY ChannelType,c_row