How to swap value in SQL Server?

Question

I want to swap the value between the row but I don't have an idea how to make it as I create a script to do it but the answer still not correct. I get the data raw in from the pdf then convert it into excel 'xls'.

Please see the sample below.

DECLARE @MyTable TABLE(Id INT, RoomNo INT, Class VARCHAR(10), Price VARCHAR(255))

INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 1,    21,     'A',        '43,028,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 2,    NULL,   NULL,       '43,896,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 3,    20,     'A',        '42,681,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 4,    NULL,   NULL,       '43,549,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 5,    19,     'A',        '42,334,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 6,    NULL,   NULL,       '43,202,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 7,    18,     'A',        '41,987,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 8,    NULL,   NULL,       '42,855,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 9,    17,     'A',        '41,467,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 10,   NULL,   NULL,       '42,334,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 11,   16,     'A',        '41,120,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 12,   NULL,   NULL,       '41,987,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 13,   NULL,   'A',        '39,211,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 14,   9,      NULL,       '40,079,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 15,   NULL,   NULL,       '38,691,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 16,   6,      NULL,       '39,385,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 17,   NULL,   'A',        '44,756,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 18,   5,      NULL,       '45,591,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 19,   21,     'B',        '26,598,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 20,   20,     'B',        '26,393,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 21,   NULL,   NULL,       '26,905,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 22,   19,     'B',        '26,189,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 23,   NULL,   NULL,       '26,700,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 24,   18,     'B',        '25,984,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 25,   NULL,   NULL,       '26,496,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 26,   17,     'B',        '25,677,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 27,   NULL,   'B',        '24,041,000'
INSERT INTO @MyTable(Id, RoomNo, Class, Price) SELECT 28,   6,      NULL,       '24,552,000'

what i tried

SELECT
        *,
        ISNULL(RoomNo, (SELECT TOP 1 RoomNo FROM @MyTable WHERE Id < MyTable.Id AND RoomNo IS NOT NULL ORDER BY ID DESC)),
        ISNULL(Class, (SELECT TOP 1 Class FROM @MyTable WHERE Id < MyTable.Id AND Class IS NOT NULL ORDER BY ID DESC)),
        Price
    FROM @MyTable as MyTable

Result is wrong:

Id  RoomNo  Class   Price       NewRoomNo   NewClass    Price
1   21      A       43,028,000  21          A           43,028,000
2   NULL    NULL    43,896,000  21          A           43,896,000
3   20      A       42,681,000  20          A           42,681,000
4   NULL    NULL    43,549,000  20          A           43,549,000
5   19      A       42,334,000  19          A           42,334,000
6   NULL    NULL    43,202,000  19          A           43,202,000
7   18      A       41,987,000  18          A           41,987,000
8   NULL    NULL    42,855,000  18          A           42,855,000
9   17      A       41,467,000  17          A           41,467,000
10  NULL    NULL    42,334,000  17          A           42,334,000
11  16      A       41,120,000  16          A           41,120,000
12  NULL    NULL    41,987,000  16          A           41,987,000
13  NULL    A       39,211,000  16          A           39,211,000
14  9       NULL    40,079,000  9           A           40,079,000
15  NULL    NULL    38,691,000  9           A           38,691,000
16  6       NULL    39,385,000  6           A           39,385,000
17  NULL    A       44,756,000  6           A           44,756,000
18  5       NULL    45,591,000  5           A           45,591,000
19  21      B       26,598,000  21          B           26,598,000
20  20      B       26,393,000  20          B           26,393,000
21  NULL    NULL    26,905,000  20          B           26,905,000
22  19      B       26,189,000  19          B           26,189,000
23  NULL    NULL    26,700,000  19          B           26,700,000
24  18      B       25,984,000  18          B           25,984,000
25  NULL    NULL    26,496,000  18          B           26,496,000
26  17      B       25,677,000  17          B           25,677,000
27  NULL    B       24,041,000  17          B           24,041,000
28  6       NULL    24,552,000  6           B           24,552,000

I want to achieve:

Id  RoomNo  Class   Price       NewRoomNo   NewClass    Price
1   21      A       43,028,000  NULL        NULL        43,028,000
2   NULL    NULL    43,896,000  21          A           43,896,000
3   20      A       42,681,000  NULL        NULL        42,681,000
4   NULL    NULL    43,549,000  20          A           43,549,000
5   19      A       42,334,000  NULL        NULL        42,334,000
6   NULL    NULL    43,202,000  19          A           43,202,000
7   18      A       41,987,000  NULL        NULL        41,987,000
8   NULL    NULL    42,855,000  18          A           42,855,000
9   17      A       41,467,000  NULL        NULL        41,467,000
10  NULL    NULL    42,334,000  17          A           42,334,000
11  16      A       41,120,000  NULL        NULL        41,120,000
12  NULL    NULL    41,987,000  16          A           41,987,000
13  NULL    A       39,211,000  NULL        NULL        39,211,000
14  9       NULL    40,079,000  9           A           40,079,000
15  NULL    NULL    38,691,000  NULL        NULL        38,691,000
16  6       NULL    39,385,000  6           A           39,385,000
17  NULL    A       44,756,000  NULL        NULL        44,756,000
18  5       NULL    45,591,000  5           A           45,591,000
19  21      B       26,598,000  21          B           26,598,000
20  20      B       26,393,000  NULL        NULL        26,393,000
21  NULL    NULL    26,905,000  20          B           26,905,000
22  19      B       26,189,000  NULL        NULL        26,189,000
23  NULL    NULL    26,700,000  19          B           26,700,000
24  18      B       25,984,000  NULL        NULL        25,984,000
25  NULL    NULL    26,496,000  18          B           26,496,000
26  17      B       25,677,000  17          B           25,677,000
27  NULL    B       24,041,000  NULL        NULL        24,041,000
28  6       NULL    24,552,000  6           B           24,552,000

It's helpful to tag database questions with both the appropriate software (MySQL, Oracle, DB2, ...) and version, e.g. `sql-server-2014`. Differences in syntax and features often affect the answers. Note that `tsql` narrows the choices, but does not specify the database. — HABO, Jul 07 '17 at 01:58
can you explain the logic like what you want and what are the rules? — ViKiNG, Jul 07 '17 at 03:21
@Viking the logic when RoomNo, Class has a null value then get the value on top of it then set the previous top into null. — PAT, Jul 07 '17 at 03:28

score 2 · Answer 1 · answered Jul 07 '17 at 10:00

2

SELECT 

Id,

RoomNo,

Class,

Price,

CASE WHEN RoomNo IS NULL THEN lag(RoomNo) over(Order by Id)

ELSE NULL END as NewRoomNo,

CASE WHEN Class IS NULL THEN lag(Class) over(Order by Id)

ELSE NULL END as NewClass,

Price

FROM @MyTable

answered Jul 07 '17 at 10:00

SQL006

439
6
21

score 1 · Answer 2 · answered Jul 07 '17 at 04:10

Not an exact answer but following should give you a clear idea and you can play on top of that to see what else suits your detailed requirements.

SELECT
    Id,
    NULLIF(RoomNo,LAG(RoomNo) OVER (ORDER BY ID)) RoomNo,
    NULLIF(Class,LAG(Class) OVER (ORDER BY ID)) Class,
    Price,
    LAG(RoomNo) OVER (ORDER BY ID) AS NewRoomNo,
    LAG(Class) OVER (ORDER BY ID)  AS NewClass,
    Price As Price2
FROM @MyTable as MyTable

ps_prakash02 · Answer 3 · 2017-07-07T07:17:34.340

1

Can you try the following,

    select  a.Id, a.RoomNo, a.Class, a.Price, 
        b.Id Idb,
        case when a.RoomNo IS NULL THEN b.RoomNo 
             else NULL
        end [swap roomno], 
        case when a.Class IS NULL THEN b.Class 
             else NULL
        end [swap class]
from @mytable a
    left join @mytable b on a.Id - 1 = b.Id

edited Jul 07 '17 at 07:17

answered Jul 07 '17 at 07:03

ps_prakash02

543
4
18

score 1 · Answer 4 · answered Jul 07 '17 at 08:00

Just by using NULLIF you can get your desired Result

SELECT Id
    ,RoomNo
    ,Class
    ,Price
    ,NULLIF(ISRoomNo, RoomNo) NewRoomNo
    ,NULLIF(ISCLASS, Class) AS NewClass
    ,Price
FROM (

SELECT
        *,
        ISNULL(RoomNo, (SELECT TOP 1 RoomNo FROM @MyTable WHERE Id < MyTable.Id AND RoomNo IS NOT NULL ORDER BY ID DESC)) ISRoomNo,
        ISNULL(Class, (SELECT TOP 1 Class FROM @MyTable WHERE Id < MyTable.Id AND Class IS NOT NULL ORDER BY ID DESC)) ISCLASS

 FROM @MyTable as MyTable
    )DT

Von Abanes · Answer 5 · 2017-07-07T09:15:32.480

Using LEAD and LAG , I came up into this.

            ;WITH    CTE
                              AS ( SELECT   id ,
                                            RoomNo ,
                                            Class ,
                                            price ,
                                            IIF(( LEAD(RoomNo, 1) OVER ( ORDER BY id ) IS NULL
                                                  AND LEAD(Class, 1) OVER ( ORDER BY id ) IS NULL
                                                ), NULL, IIF(LAG(RoomNo, 1) OVER ( ORDER BY id ) IS NOT NULL
                                            AND LAG(Class, 1) OVER ( ORDER BY id ) IS NOT NULL, LAG(RoomNo,
                                                                                  1) OVER ( ORDER BY id ), NULL)) NewRoomNo ,
                                            IIF(( LEAD(RoomNo, 1) OVER ( ORDER BY id ) IS NULL
                                                  AND LEAD(Class, 1) OVER ( ORDER BY id ) IS NULL
                                                ), NULL, IIF(LAG(RoomNo, 1) OVER ( ORDER BY id ) IS NOT NULL
                                            AND LAG(Class, 1) OVER ( ORDER BY id ) IS NOT NULL, LAG(Class,
                                                                                  1) OVER ( ORDER BY id ), NULL)) NewClass ,
                                            price NewPrice
                                   FROM     @MyTable
                                 ),
                            CTE2
                              AS ( SELECT   id ,
                                            RoomNo ,
                                            Class ,
                                            price ,
                                            IIF(RoomNo IS NOT NULL
                                            AND Class IS NULL
                                            AND LAG(RoomNo, 1) OVER ( ORDER BY id ) IS NULL
                                            AND LAG(class, 1) OVER ( ORDER BY id ) IS NOT NULL, RoomNo, NewRoomNo) NewRoom ,
                                            IIF(RoomNo IS NOT NULL
                                            AND Class IS NULL
                                            AND LAG(RoomNo, 1) OVER ( ORDER BY id ) IS NULL
                                            AND LAG(class, 1) OVER ( ORDER BY id ) IS NOT NULL, LAG(class,
                                                                                  1) OVER ( ORDER BY id ), NewClass) NewClass ,
                                            NewPrice
                                   FROM     cte
                                 ),
                            CTE3
                              AS ( SELECT   id ,
                                            RoomNo ,
                                            Class ,
                                            price ,
                                            IIF(( RoomNo IS NOT NULL
                                                  AND Class IS NULL
                                                  AND LEAD(RoomNo, 1) OVER ( ORDER BY id ) IS NULL
                                                  AND LEAD(class, 1) OVER ( ORDER BY id ) IS NOT NULL
                                                ), RoomNo, NewRoom) NewRoom ,
                                            IIF(( RoomNo IS NOT NULL
                                                  AND Class IS NULL
                                                  AND LEAD(RoomNo, 1) OVER ( ORDER BY id ) IS NULL
                                                  AND LEAD(class, 1) OVER ( ORDER BY id ) IS NOT NULL
                                                ), LEAD(class, 1) OVER ( ORDER BY id ), NewClass) NewClass ,
                                            NewPrice
                                   FROM     CTE2
                                 ),
                            CTE4
                              AS ( SELECT   id ,
                                            RoomNo ,
                                            Class ,
                                            price ,
                                            IIF(RoomNo IS NOT NULL
                                            AND class IS NOT NULL
                                            AND LEAD(RoomNo, 1) OVER ( ORDER BY id ) IS NULL
                                            AND LEAD(class, 1) OVER ( ORDER BY id ) IS NULL, RoomNo, NewRoom) NewRoom ,
                                            IIF(RoomNo IS NOT NULL
                                            AND class IS NOT NULL
                                            AND LEAD(RoomNo, 1) OVER ( ORDER BY id ) IS NULL
                                            AND LEAD(class, 1) OVER ( ORDER BY id ) IS NULL, Class, NewClass) NewClass ,
                                            NewPrice
                                   FROM     CTE3
                                 ),
                            CTE5
                              AS ( SELECT   id ,
                                            roomNo ,
                                            Class ,
                                            price ,
                                            IIF(RoomNo IS NOT NULL
                                            AND class IS NOT NULL
                                            AND LEAD(RoomNo, 1) OVER ( ORDER BY id ) IS NULL
                                            AND LEAD(class, 1) OVER ( ORDER BY id ) IS NOT NULL, RoomNo, NewRoom) NewRoom ,
                                            IIF(RoomNo IS NOT NULL
                                            AND class IS NOT NULL
                                            AND LEAD(RoomNo, 1) OVER ( ORDER BY id ) IS NULL
                                            AND LEAD(class, 1) OVER ( ORDER BY id ) IS NOT NULL, Class, NewClass) NewClass ,
                                            NewPrice
                                   FROM     CTE4
                                 ),
                            CTE6
                              AS ( SELECT   id ,
                                            roomNo ,
                                            Class ,
                                            price ,
                                            IIF(roomNo IS NULL
                                            AND class IS NOT NULL
                                            AND LAG(roomno, 1) OVER ( ORDER BY id ) IS NOT NULL
                                            AND LAG(class, 1) OVER ( ORDER BY id ) IS NOT NULL, NULL, newRoom) newRoom ,
                                            IIF(roomNo IS NULL
                                            AND class IS NOT NULL
                                            AND LAG(roomno, 1) OVER ( ORDER BY id ) IS NOT NULL
                                            AND LAG(class, 1) OVER ( ORDER BY id ) IS NOT NULL, NULL, NewClass) NewClass ,
                                            NewPrice
                                   FROM     CTE5
                                 ),
                            CTE7
                              AS ( SELECT   id ,
                                            roomNo ,
                                            Class ,
                                            price ,
                                            IIF(RoomNo IS NOT NULL
                                            AND class IS NOT NULL
                                            AND LEAD(RoomNo, 1) OVER ( ORDER BY id ) IS NULL
                                            AND LEAD(class, 1) OVER ( ORDER BY id ) IS  NULL, NULL, NewRoom) NewRoom ,
                                            IIF(RoomNo IS NOT NULL
                                            AND class IS NOT NULL
                                            AND LEAD(RoomNo, 1) OVER ( ORDER BY id ) IS NULL
                                            AND LEAD(class, 1) OVER ( ORDER BY id ) IS  NULL, NULL, NewClass) NewClass ,
                                            NewPrice
                                   FROM     CTE6
                                 )
                        SELECT  id ,
                                roomNo ,
                                Class ,
                                price ,
                                IIF(LAG(roomno, 1) OVER ( ORDER BY id ) IS NOT NULL
                                AND LAG(Class, 1) OVER ( ORDER BY id ) IS NULL
                                AND roomno IS NOT NULL
                                AND class IS NOT NULL, roomno, NewRoom) NewRoom ,
                                IIF(LAG(roomno, 1) OVER ( ORDER BY id ) IS NOT NULL
                                AND LAG(Class, 1) OVER ( ORDER BY id ) IS NULL
                                AND roomno IS NOT NULL
                                AND class IS NOT NULL, Class, NewClass) NewClass ,
                                NewPrice price
                        FROM    CTE7

RESULT:

                id          roomNo      Class      price           NewRoom     NewClass   price
                ----------- ----------- ---------- --------------- ----------- ---------- ------------------
                1           21          A          43,028,000      NULL        NULL       43,028,000
                2           NULL        NULL       43,896,000      21          A          43,896,000
                3           20          A          42,681,000      NULL        NULL       42,681,000
                4           NULL        NULL       43,549,000      20          A          43,549,000
                5           19          A          42,334,000      NULL        NULL       42,334,000
                6           NULL        NULL       43,202,000      19          A          43,202,000
                7           18          A          41,987,000      NULL        NULL       41,987,000
                8           NULL        NULL       42,855,000      18          A          42,855,000
                9           17          A          41,467,000      NULL        NULL       41,467,000
                10          NULL        NULL       42,334,000      17          A          42,334,000
                11          16          A          41,120,000      NULL        NULL       41,120,000
                12          NULL        NULL       41,987,000      16          A          41,987,000
                13          NULL        A          39,211,000      NULL        NULL       39,211,000
                14          9           NULL       40,079,000      9           A          40,079,000
                15          NULL        NULL       38,691,000      NULL        NULL       38,691,000
                16          6           NULL       39,385,000      6           A          39,385,000
                17          NULL        A          44,756,000      NULL        NULL       44,756,000
                18          5           NULL       45,591,000      5           A          45,591,000
                19          21          B          26,598,000      21          B          26,598,000
                20          20          B          26,393,000      NULL        NULL       26,393,000
                21          NULL        NULL       26,905,000      20          B          26,905,000
                22          19          B          26,189,000      NULL        NULL       26,189,000
                23          NULL        NULL       26,700,000      19          B          26,700,000
                24          18          B          25,984,000      NULL        NULL       25,984,000
                25          NULL        NULL       26,496,000      18          B          26,496,000
                26          17          B          25,677,000      17          B          25,677,000
                27          NULL        B          24,041,000      NULL        NULL       24,041,000     
                28          6           NULL       24,552,000      6           B          24,552,000

                (28 row(s) affected)

Thank you Von! nice answer but the script is too long can please simplify :) — PAT, Jul 10 '17 at 03:49

How to swap value in SQL Server?

5 Answers5