I couldn't understand fully your requirements,
because your example was too small and I thought that your description was contradicted with your example.
Your output rows in the example seems to be corresponded one by one with your input rows.
Anyhow, your output would be produced like this:
Code:
------------------------------ Commands Entered ------------------------------
WITH
input(col1, col2, col3) AS (
VALUES
(1, 'A', '&')
,(2, 'B', '$')
,(3, 'C', '#')
,(4, 'D', '@')
)
SELECT (col1 + 1) / 2 AS col1
, col2
, col3
, MOD(col1 - 1, 2) + 1 AS col4
FROM input
;
------------------------------------------------------------------------------
COL1 COL2 COL3 COL4
----------- ---- ---- -----------
1 A & 1
1 B $ 2
2 C # 1
2 D @ 2
4 record(s) selected.
If col1 were not consecutive, ROW_NUMBER OLAP function would be a solution:
Code:
------------------------------ Commands Entered ------------------------------
WITH
input(col1, col2, col3) AS (
VALUES
(1, 'A', '&')
,(2, 'B', '$')
,(3, 'C', '#')
,(4, 'D', '@')
,(6, 'E', '%')
,(8, 'F', '*')
,(9, 'G', '/')
)
SELECT (col1_seq + 1) / 2 AS col1
, col2
, col3
, MOD(col1_seq - 1, 2) + 1 AS col4
FROM (SELECT ip.*
, INTEGER( ROW_NUMBER() OVER(ORDER BY col1) ) AS col1_seq
FROM input AS ip
) AS s
;
------------------------------------------------------------------------------
COL1 COL2 COL3 COL4
----------- ---- ---- -----------
1 A & 1
1 B $ 2
2 C # 1
2 D @ 2
3 E % 1
3 F * 2
4 G / 1
7 record(s) selected.