CODEWITHSUNDEEP
sql-server
Ramana
Ramana

Reputation: 25

Copy previous row value to all next rows in sql

I have requirement to copy previous rows values to next all rows in sql server.

With LAG function, I can achieve this one only for next row. but I have to copy more than rows.

Here is sample example :

Attaching Image for expected result

Upvotes: 0

Views: 2735

Answers (3)

gotqn
gotqn

Reputation: 43626

You can try this:

SELECT *
      ,ISNULL(b.flag,(MAX(b.flag) OVER (PARTITION BY a.[custid] ORDER BY b.[id] DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)))
FROM @TBL1 A
LEFT JOIN @TBL2 B
    ON A.id = b.id
    AND A.custid = b.custid
ORDER BY a.[custid] ASC, a.[id] DESC

Here is the sample data:

DECLARE @TBL1 TABLE
(
    [id] TINYINT
   ,[custid] TINYINT
);

DECLARE @TBL2 TABLE
(
    [id] TINYINT
   ,[custid] TINYINT
   ,[flag] TINYINT
);

INSERT INTO @TBL1([id], [custid])
VALUES 
(1,11)
,(1,12)
,(1,13)
,(1,14)
,(1,15)
,(1,16)
,(1,17)
,(2,11)
,(2,12)
,(2,13)
,(2,14)
,(2,15)
,(2,16)
,(2,17)
,(3,11)
,(3,12)
,(3,13)
,(3,14)
,(3,15)
,(3,16)
,(3,17)
,(4,11)
,(4,12)
,(4,13)
,(4,14)
,(4,15)
,(4,16)
,(4,17);

INSERT INTO @TBL2 ([id], [custid], [flag])
VALUES (1,11,0)
,(1,12,1)
,(1,13,1)
,(1,14,0)
,(1,15,0)
,(1,16,0)
,(1,17,1)
,(2,11,1)
,(2,13,0)
,(2,14,1)
,(2,15,1)
,(2,17,0)
,(3,13,1)
,(3,15,0)
,(3,17,1)
,(4,12,0)
,(4,17,0)

Upvotes: 1

Ehryk
Ehryk

Reputation: 1990

Query:

SELECT
    t1.ID,
    t1.CustID,
    t2.ID,
    t2.CustID,
    t2.Flag,
    COALESCE(
        t2.Flag,
        (
            SELECT TOP 1 l.Flag 
            FROM TBL2 l
            WHERE l.CustID = t1.CustID AND l.ID < t1.ID
            ORDER BY l.ID desc
        )) as 'Final'
FROM
    TBL1 t1
    LEFT OUTER JOIN TBL2 t2 ON t2.ID = t1.ID AND t2.CustID = t1.CustID
ORDER BY
    t1.CustID,
    t1.ID desc

Setup:

CREATE TABLE TBL1 (ID int, CustID int)
GO

CREATE TABLE TBL2 (ID int, CustID int, Flag bit)
GO

INSERT INTO TBL1 (ID, CustID)
SELECT 1, 11 UNION ALL
SELECT 1, 12 UNION ALL
SELECT 1, 13 UNION ALL
SELECT 1, 14 UNION ALL
SELECT 1, 15 UNION ALL
SELECT 1, 16 UNION ALL
SELECT 1, 17 UNION ALL
SELECT 2, 11 UNION ALL
SELECT 2, 12 UNION ALL
SELECT 2, 13 UNION ALL
SELECT 2, 14 UNION ALL
SELECT 2, 15 UNION ALL
SELECT 2, 16 UNION ALL
SELECT 2, 17 UNION ALL
SELECT 3, 11 UNION ALL
SELECT 3, 12 UNION ALL
SELECT 3, 13 UNION ALL
SELECT 3, 14 UNION ALL
SELECT 3, 15 UNION ALL
SELECT 3, 16 UNION ALL
SELECT 3, 17 UNION ALL
SELECT 4, 11 UNION ALL
SELECT 4, 12 UNION ALL
SELECT 4, 13 UNION ALL
SELECT 4, 14 UNION ALL
SELECT 4, 15 UNION ALL
SELECT 4, 16 UNION ALL
SELECT 4, 17

GO

INSERT INTO TBL2 (ID, CustID, Flag)
SELECT 1, 11, 0 UNION ALL
SELECT 1, 12, 1 UNION ALL
SELECT 1, 13, 1 UNION ALL
SELECT 1, 14, 0 UNION ALL
SELECT 1, 15, 0 UNION ALL
SELECT 1, 16, 0 UNION ALL
SELECT 1, 17, 1 UNION ALL
SELECT 2, 11, 1 UNION ALL
SELECT 2, 13, 0 UNION ALL
SELECT 2, 14, 1 UNION ALL
SELECT 2, 15, 1 UNION ALL
SELECT 2, 17, 0 UNION ALL
SELECT 3, 13, 1 UNION ALL
SELECT 3, 15, 0 UNION ALL
SELECT 3, 17, 1 UNION ALL
SELECT 4, 12, 0 UNION ALL
SELECT 4, 17, 0

GO

Upvotes: 1

sagi
sagi

Reputation: 40471

You can use a LEFT JOIN ,a CASE EXPRESSION and a correlated query :

SELECT t.id,t.custid,s.id,s.custid,
       COALESCE(s.flag,(SELECT TOP 1 ss.flag FROM tbl2 ss
                        WHERE ss.custid = t.custid and ss.id < t.id
                        ORDER BY ss.id DESC)) as flag
FROM Tbl1 t
LEFT JOIN tbl2 s
 ON(t.custid = s.custid and s.id = t.id)

Upvotes: 0

Related Questions