Ivan-Mark Debono Ivan-Mark Debono - 3 months ago 8
SQL Question

How to transform every 2 rows into 1 row?

I have the following tables:

CREATE TABLE source_table (
id int IDENTITY(1, 1) NOT NULL,
category varchar(255) NULL,
item int NULL,
counter int NULL,
length int NULL
);

CREATE TABLE dest_table(
id int IDENTITY(1, 1) NOT NULL,
from_item [int] NULL,
to_item [int] NULL,
length [int] NULL
);


The source table contains the following records:

INSERT INTO source_table SELECT 'A', 100, 1, 0
INSERT INTO source_table SELECT 'A', 101, 2, 10
INSERT INTO source_table SELECT 'A', 102, 3, 5
INSERT INTO source_table SELECT 'A', 103, 4, 7
INSERT INTO source_table SELECT 'A', 104, 5, 12
INSERT INTO source_table SELECT 'B', 101, 1, 0
INSERT INTO source_table SELECT 'B', 111, 2, 15
INSERT INTO source_table SELECT 'B', 114, 3, 6
INSERT INTO source_table SELECT 'B', 117, 4, 13
INSERT INTO source_table SELECT 'B', 119, 5, 8


The rows from the source table need to be transformed in such a way so that each record in the destination table would represent 2 rows from the source table.

What is the correct SQL syntax to transform the above rows as the following in the destination table?

100, 101, 10
101, 102, 5
102, 103, 7
103, 104, 12
101, 111, 15
111, 114, 6
114, 117, 13
117, 119, 8

Answer

Do a SELF JOIN.

Query

insert into dest_table([from_item], [to_item], [length])
select t.* from(
    select t1.[item] as col_1, t2.[item] as col_2, t2.[length]
    from source_table t1
    join source_table t2
    on t1.id = t2.id -1
)t
where t.[length] <> 0;

The result set looks like below.

+-----+-----+------+
|col_1|col_2|length|
+-----+-----+------+
| 100 | 101 | 10   |
| 101 | 102 | 5    |
| 102 | 103 | 7    |
| 103 | 104 | 12   |
| 101 | 111 | 15   |
| 111 | 114 | 6    |
| 114 | 117 | 13   |
| 117 | 119 | 8    |
+-----+-----+------+

Edit

If there is gap in the identity column. Then,

Query

;with cte as(
    select rn = row_number() over(
        order by id
    ), *
    from source_table
)
insert into dest_table([from_item], [to_item], [length])
select t.* from(
    select t1.[item] as col_1, t2.[item] as col_2, t2.[length]
    from cte t1
    join cte t2
    on t1.id = t2.id -1
)t
where t.[length] <> 0;