MS sql null or Empty column value fill in up value-CodePudding

TABLE -A

ID	NAME
1	Iqbal
2
3
4	Hossain
5
6
7
8	ABC
9	DEF

TABLE -A Column Name some value is empty

TABLE -B

ID	NAME
1	Iqbal
2	Hossain
3	Hossain
4	Hossain
5	ABC
6	ABC
7	ABC
8	ABC
9	DEF

How can i select Table -A Column **Name ** value same as Table -B

Try to get result same as Table -B

CodePudding user response：

SELECT 
  Table_A.Id,
  ISNULL(TABLE_A.Name, TABLE_B.Name) As Name
FROM
  (
  VALUES
(1,     'Iqbal'),
(2,     NULL),
(3,     NULL),
(4,     'Hossain'),
(5,     NULL),
(6,     NULL),
(7,     NULL),
(8,     'ABC'),
(9,     'DEF')
  ) TABLE_A  (ID, name)
LEFT OUTER JOIN 
(
  VALUES
(1,     'Iqbal'),
(2,     'Hossain'),
(3,     'Hossain'),
(4,     'Hossain'),
(5,     'ABC'),
(6,     'ABC'),
(7,     'ABC'),
(8,     'ABC'),
(9,     'DEF')
) TABLE_B  (ID, name)
ON
TABLE_A.Id = Table_B.Id

CodePudding user response：

select * into #tt from (select 
1 as id,    'Iqbal' as name 
UNION ALL
select  2 , '' UNION ALL
select  3 , '' UNION ALL
select  4 , 'Hossain' UNION ALL
select  5 , '' UNION ALL
select  6 , '' UNION ALL 
select  7 , '' UNION ALL
select  8 , 'ABC' UNION ALL
select  9 , 'DEF'
)tt    

SELECT 
        id,
        CASE WHEN (NAME IS NULL OR name = '') THEN 
            (SELECT TOP 1 name FROM #tt tr WHERE (name IS NOT NULL AND name <> '') AND tr.id > rr.id ) 
        ELSE name END
    FROM #tt rr