CODEWITHSUNDEEP
sqlsql-servert-sql
user4834772
user4834772

Reputation:

how to fill up the gaps in Rows in a table

I have mart table where i have gaps in rows.I tried using loop condition but I'm unable to proceed 

CREATE TABLE Mart
    (martID int, mart int)
;

INSERT INTO Mart
    (martID, mart)
VALUES
    (1, 10),
    (4, 12),
    (6, 20)
;

OutPut 

martID       mart
1             10
2             0
3             0
4             12
5             0
6             20

My code so far

select  max(martId) as nr
        from    Mart
        union all
        select  nr - 1
        from    numbers
        where   nr > 1

Upvotes: 2

Views: 101

Answers (4)

Pரதீப்
Pரதீப்

Reputation: 93734

Hope you have Number table contains series of Numbers without gaps. Try this

SELECT nr, 
       COALESCE(mart, 0) AS mart 
FROM   numbers n 
       LEFT OUTER JOIN mart m 
                    ON m.martid = n.nr 
WHERE  n.nr BETWEEN (SELECT Min(martid) 
                     FROM   mart) AND (SELECT Max(martid) 
                                       FROM   mart)

In case you don't have numbers table then refer this link to generate sequence of values in SQL Server. I will prefer STACKED CTE method

;WITH e1(n) AS
(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), -- 10
e2(n) AS (SELECT 1 FROM e1 CROSS JOIN e1 AS b), -- 10*10
e3(n) AS (SELECT 1 FROM e1 CROSS JOIN e2) -- 10*100
  SELECT n = ROW_NUMBER() OVER (ORDER BY n) FROM e3 ORDER BY n;

Upvotes: 1

PP006
PP006

Reputation: 709

Try using recursive cte,

DECLARE @Max_var INT=(SELECT Max(martid)
  FROM   Mart);

WITH cte
     AS (SELECT 1 AS Value
         UNION ALL
         SELECT Value=Value + 1
         FROM   cte
         WHERE  ( Value + 1 ) <= @Max_var)
SELECT Value,
       Isnull(mart, 0)
FROM   cte A
       LEFT JOIN Mart B
              ON A.Value = B.martID

Upvotes: 0

dee.ronin
dee.ronin

Reputation: 1038

You can use the code below if you just want to display the data:

SELECT 
    n.MartId, 
    ISNULL(m.mart, 0)
FROM 
    numbers n
    LEFT JOIN Mart m ON n.MartId = m.martID
ORDER BY
    n.MartId

Upvotes: 1

mohan111
mohan111

Reputation: 8865

may be this works 

declare @Mart TABLE 
    (martID int, mart int)
;

INSERT INTO @Mart
    (martID, mart)
VALUES
    (1, 10),
    (4, 12),
    (6, 20)
;
declare @MinNo int
declare @MaxNo int
declare @IncrementStep int
set @MinNo = 1
set @MaxNo = 10
set @IncrementStep = 1

;with C as
(
  select @MinNo as Num
  union all 
  select Num + @IncrementStep
  from C 
  where Num < @MaxNo
)      
select Num,
CASE WHEN mart IS NOT NULL THEN mart ELSE 0 END AS NUMBER
from C  
LEFT JOIN @Mart t
ON t.martID = c.Num

Upvotes: 0

Related Questions