mohan111 mohan111 - 4 months ago 8
SQL Question

How to get missing values in gaps of Table Data

How to fill the gaps of table . I have a sample data

DECLARE @Table TABLE
( Rule_ID VARCHAR(10),
Name VARCHAR(10),
Age INT
)
INSERT INTO @Table(Rule_ID,Name,Age)
VALUES
('Rule_01','Mohan',29),
('Rule_01','Manasa',25),
('Rule_03','Raju',29),
('Rule_03','Miju',25),
('Rule_05','Ramu',30),
('Rule_05','Rao',35)

Select * from @Table


Present Result Set

Rule_ID Name Age

Rule_01 Mohan 29
Rule_01 Manasa 25
Rule_03 Raju 29
Rule_03 Miju 25
Rule_05 Ramu 30
Rule_05 Rao 35


I want output like this

Rule_ID Name Age

Rule_01 Mohan 29
Rule_01 Manasa 25
Rule_02 NULL NULL
Rule_03 Raju 29
Rule_03 Miju 25
Rule_04 NULL NULL
Rule_05 Ramu 30
Rule_05 Rao 35


I have tried lot of answers from Stack overflow. But I'm unable to move forward . Suggest me

Answer

Depands on Rule_ID. Work with last two digit of Rule_ID.

DECLARE @Table TABLE 
( Rule_ID VARCHAR(10),
  Name    VARCHAR(10),
  Age     INT
)
INSERT INTO @Table(Rule_ID,Name,Age) 
VALUES
('Rule_01','Mohan',29),
('Rule_01','Manasa',25),
('Rule_03','Raju',29),
('Rule_03','Miju',25),
('Rule_05','Ramu',30),
('Rule_05','Rao',35)

DECLARE @MaxValue INT
SELECT @MaxValue = MAX(CAST(RIGHT(Rule_ID, 2) AS INT)) from @Table

DECLARE @Id INT = 1
;WITH Tmp (Id)
AS 
(
    SELECT @Id AS Id
    UNION ALL
    SELECT Id +1 FROM Tmp
    WHERE
        Id < @MaxValue      
)

SELECT * FROM @Table
UNION ALL
SELECT 'Rule_' + RIGHT('00' + CAST(A.Id AS NVARCHAR(2)), 2), NULL, null FROM Tmp A
WHERE
    NOT EXISTS
    (
        SELECT TOP 1 1 FROM @Table T
        WHERE
            T.Rule_ID = 'Rule_' + RIGHT('00' + CAST(A.Id AS NVARCHAR(2)), 2)
    )
ORDER BY Rule_ID

Produced

Rule_ID    Name       Age
---------- ---------- -----------
Rule_01    Mohan      29
Rule_01    Manasa     25
Rule_02    NULL       NULL
Rule_03    Raju       29
Rule_03    Miju       25
Rule_04    NULL       NULL
Rule_05    Ramu       30
Rule_05    Rao        35
Comments