CODEWITHSUNDEEP
sqlsql-servert-sql
Vipin Kumar
Vipin Kumar

Reputation: 136

copy row data from previous row

I have a table like below:

╔══════════════════════════════════════╦═══════════════════════╗
║                  Id                  ║ ContiguousSubnetStart ║
╠══════════════════════════════════════╬═══════════════════════╣
║ 53DC370E-8C7D-4526-9292-35125443E4B1 ║ 10.60.66.8            ║
║ 53DC370E-8C7D-4526-9292-35125443E4B1 ║ 1                     ║
║ 53DC370E-8C7D-4526-9292-35125443E4B1 ║ 10.60.66.18           ║
║ 53DC370E-8C7D-4526-9292-35125443E4B1 ║ 1                     ║
║ 53DC370E-8C7D-4526-9292-35125443E4B1 ║ 1                     ║
║ 53DC370E-8C7D-4526-9292-35125443E4B1 ║ 1                     ║
║ 53DC370E-8C7D-4526-9292-35125443E4B1 ║ 1                     ║
║ B6F8484C-B8F9-4CB0-B6BF-395A7599FFB7 ║ 10.60.88.28           ║
║ B6F8484C-B8F9-4CB0-B6BF-395A7599FFB7 ║ 1                     ║
║ B6F8484C-B8F9-4CB0-B6BF-395A7599FFB7 ║ 1                     ║
║ B6F8484C-B8F9-4CB0-B6BF-395A7599FFB7 ║ 1                     ║
╚══════════════════════════════════════╩═══════════════════════╝

I would like this table to transform into:

╔══════════════════════════════════════╦═══════════════════════╗
║                  Id                  ║ ContiguousSubnetStart ║
╠══════════════════════════════════════╬═══════════════════════╣
║ 53DC370E-8C7D-4526-9292-35125443E4B1 ║ 10.60.66.8            ║
║ 53DC370E-8C7D-4526-9292-35125443E4B1 ║ 10.60.66.8            ║
║ 53DC370E-8C7D-4526-9292-35125443E4B1 ║ 10.60.66.18           ║
║ 53DC370E-8C7D-4526-9292-35125443E4B1 ║ 10.60.66.18           ║
║ 53DC370E-8C7D-4526-9292-35125443E4B1 ║ 10.60.66.18           ║
║ 53DC370E-8C7D-4526-9292-35125443E4B1 ║ 10.60.66.18           ║
║ 53DC370E-8C7D-4526-9292-35125443E4B1 ║ 10.60.66.18           ║
║ B6F8484C-B8F9-4CB0-B6BF-395A7599FFB7 ║ 10.60.88.28           ║
║ B6F8484C-B8F9-4CB0-B6BF-395A7599FFB7 ║ 10.60.88.28           ║
║ B6F8484C-B8F9-4CB0-B6BF-395A7599FFB7 ║ 10.60.88.28           ║
║ B6F8484C-B8F9-4CB0-B6BF-395A7599FFB7 ║ 10.60.88.28           ║
╚══════════════════════════════════════╩═══════════════════════╝

without using cursors. ip-address followed by 1's represent one group.

Upvotes: 1

Views: 179

Answers (2)

A. Greensmith
A. Greensmith

Reputation: 375

You could also do a WHILE loop with a row count, not sure which would be better performance wise for your environment.

CREATE TABLE #Test
(
  Id UNIQUEIDENTIFIER ,
  ContiguousSubnetStart VARCHAR(100)
)

INSERT  INTO #Test
VALUES  ( '53DC370E-8C7D-4526-9292-35125443E4B1', '10.60.66.8' ),
    ( '53DC370E-8C7D-4526-9292-35125443E4B1', '1' ),
    ( '53DC370E-8C7D-4526-9292-35125443E4B1', '10.60.66.18' ),
    ( '53DC370E-8C7D-4526-9292-35125443E4B1', '1' ),
    ( '53DC370E-8C7D-4526-9292-35125443E4B1', '1' ),
    ( '53DC370E-8C7D-4526-9292-35125443E4B1', '1' ),
    ( '53DC370E-8C7D-4526-9292-35125443E4B1', '1' ),
    ( 'B6F8484C-B8F9-4CB0-B6BF-395A7599FFB7', '10.60.88.28' ),
    ( 'B6F8484C-B8F9-4CB0-B6BF-395A7599FFB7', '1' ),
    ( 'B6F8484C-B8F9-4CB0-B6BF-395A7599FFB7', '1' ),
    ( 'B6F8484C-B8F9-4CB0-B6BF-395A7599FFB7', '1' )

SELECT *, [RN] = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) INTO #NewTable FROM #Test

DECLARE @Count INT = (SELECT MAX(RN) FROM #NewTable)
DECLARE @CurrentRow INT = 1

WHILE @Count > 0
BEGIN
    UPDATE #NewTable
    SET ContiguousSubnetStart = (SELECT ContiguousSubnetStart FROM #NewTable WHERE RN = @CurrentRow - 1)
    WHERE ContiguousSubnetStart = '1'
    AND RN = @CurrentRow

    SET @CurrentRow = @CurrentRow + 1
    SET @Count = @Count - 1
END

Result Set:

===========================================================
| 53DC370E-8C7D-4526-9292-35125443E4B1 | 10.60.66.8  | 1  |
| 53DC370E-8C7D-4526-9292-35125443E4B1 | 10.60.66.8  | 2  |
| 53DC370E-8C7D-4526-9292-35125443E4B1 | 10.60.66.18 | 3  |
| 53DC370E-8C7D-4526-9292-35125443E4B1 | 10.60.66.18 | 4  |
| 53DC370E-8C7D-4526-9292-35125443E4B1 | 10.60.66.18 | 5  |
| 53DC370E-8C7D-4526-9292-35125443E4B1 | 10.60.66.18 | 6  |
| 53DC370E-8C7D-4526-9292-35125443E4B1 | 10.60.66.18 | 7  |
| B6F8484C-B8F9-4CB0-B6BF-395A7599FFB7 | 10.60.88.28 | 8  |
| B6F8484C-B8F9-4CB0-B6BF-395A7599FFB7 | 10.60.88.28 | 9  |
| B6F8484C-B8F9-4CB0-B6BF-395A7599FFB7 | 10.60.88.28 | 10 |
| B6F8484C-B8F9-4CB0-B6BF-395A7599FFB7 | 10.60.88.28 | 11 |
===========================================================

Upvotes: 0

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

Such manipulations need some column on which you can order rows in order to get desired result. In the example I have manually added such a column rn using window function row_number:

DECLARE @t TABLE
    (
      Id UNIQUEIDENTIFIER ,
      ContiguousSubnetStart VARCHAR(100)
    )

INSERT  INTO @t
VALUES  ( '53DC370E-8C7D-4526-9292-35125443E4B1', '10.60.66.8' ),
        ( '53DC370E-8C7D-4526-9292-35125443E4B1', '1' ),
        ( '53DC370E-8C7D-4526-9292-35125443E4B1', '10.60.66.18' ),
        ( '53DC370E-8C7D-4526-9292-35125443E4B1', '1' ),
        ( '53DC370E-8C7D-4526-9292-35125443E4B1', '1' ),
        ( '53DC370E-8C7D-4526-9292-35125443E4B1', '1' ),
        ( '53DC370E-8C7D-4526-9292-35125443E4B1', '1' ),
        ( 'B6F8484C-B8F9-4CB0-B6BF-395A7599FFB7', '10.60.88.28' ),
        ( 'B6F8484C-B8F9-4CB0-B6BF-395A7599FFB7', '1' ),
        ( 'B6F8484C-B8F9-4CB0-B6BF-395A7599FFB7', '1' ),
        ( 'B6F8484C-B8F9-4CB0-B6BF-395A7599FFB7', '1' )


;WITH cte AS(SELECT *, ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) rn FROM @t)
SELECT oa.Id, oa.ContiguousSubnetStart 
FROM cte t1
OUTER APPLY(SELECT TOP 1 * 
            FROM cte t2 
            WHERE t1.Id = t2.Id AND t2.ContiguousSubnetStart <> '1' AND t2.rn <= t1.rn 
            ORDER BY rn DESC)oa

If you select from cte you will see:

Id                                      ContiguousSubnetStart   rn
53DC370E-8C7D-4526-9292-35125443E4B1    10.60.66.8              1
53DC370E-8C7D-4526-9292-35125443E4B1    1                       2
53DC370E-8C7D-4526-9292-35125443E4B1    10.60.66.18             3
53DC370E-8C7D-4526-9292-35125443E4B1    1                       4
53DC370E-8C7D-4526-9292-35125443E4B1    1                       5
53DC370E-8C7D-4526-9292-35125443E4B1    1                       6
53DC370E-8C7D-4526-9292-35125443E4B1    1                       7
B6F8484C-B8F9-4CB0-B6BF-395A7599FFB7    10.60.88.28             8
B6F8484C-B8F9-4CB0-B6BF-395A7599FFB7    1                       9
B6F8484C-B8F9-4CB0-B6BF-395A7599FFB7    1                       10
B6F8484C-B8F9-4CB0-B6BF-395A7599FFB7    1                       11

If you have such a column like incrementing identity or date column using which you can unambigiously order the data in your table just use that column instead of rn and you won't need cte any more. Say that column name is SomeOrderingColumn, then your statement will look like:

SELECT oa.Id, oa.ContiguousSubnetStart 
FROM TableName t1
OUTER APPLY(SELECT TOP 1 * 
            FROM TableName t2 
            WHERE t1.Id = t2.Id AND t2.ContiguousSubnetStart <> '1' 
                  AND t2.SomeOrderingColumn <= t1.SomeOrderingColumn
            ORDER BY SomeOrderingColumn DESC)oa

Without that ordering column you may still get the desired result, but that will not be guarantied under all circumstances and you can encounter some day that you are getting wrong results. This was discussed many times and you can find out that you are guarantied to get ordered results only and only if you explicitly use ORDER BY clause. But you can not order your data with guid or ip columns. So you will have to add one more column to your table that will guaranty ordering like incrementing ID or DateTime column.

Upvotes: 6

Related Questions