Remove null values ​​in data on SQL Server

In SQL Server 2008 you can use ROW_NUMBER()

to get the order of values ​​and then do a self join. Something like that.

Data

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)

      

        
        
        
      

    

Query

;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 and future releases, you can use LEAD

and COUNT() OVER()

. Something like that

;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