sec query result in to first table last row

WITH RankedTable1 AS (
    SELECT 
        Year,
        Settings_COT,
        Settings_DOT,
        Settings_POT,
        Monitoring_COT,
        Monitoring_DOT,
        ROW_NUMBER() OVER (PARTITION BY Year ORDER BY Settings_COT) AS RowNum1
    FROM Table_1
),
RankedTable2 AS (
    SELECT 
        Year,
        PerApp_Pot,
        ROW_NUMBER() OVER (PARTITION BY Year ORDER BY PerApp_Pot) AS RowNum2
    FROM Table_2
)
SELECT
    T1.Year,
    T1.Settings_COT,
    T1.Settings_DOT,
    T1.Settings_POT,
    T1.Monitoring_COT,
    T1.Monitoring_DOT,
    T2.PerApp_Pot
FROM
    RankedTable1 T1
LEFT JOIN
    RankedTable2 T2
ON
    T1.Year = T2.Year
    AND T1.RowNum1 = T2.RowNum2
ORDER BY 
    T1.Year, T1.RowNum1;

• RankedTable1:

  • We apply the ROW_NUMBER() function to Table_1 based on the Settings_COT column (you can adjust the ordering based on your requirement). This assigns a unique row number (RowNum1) to each record for a given Year.
  • We use the PARTITION BY Year clause so that the row numbers are reset for each year.

• RankedTable2:

  • Similarly, the ROW_NUMBER() function is applied to Table_2 based on the PerApp_Pot column (again, you can adjust ordering criteria). This assigns a unique row number (RowNum2) to each record for a given Year.

Hi Lejo,

CREATE TABLE Table_1 (
    Year INT,
    Settings_COT VARCHAR(50),
    Settings_DOT VARCHAR(50),
    Settings_POT VARCHAR(50),
    Monitoring_COT VARCHAR(50),
    Monitoring_DOT VARCHAR(50),
);

CREATE TABLE Table_2 (
    Year INT,
    PerApp_Pot VARCHAR(50)
);

INSERT INTO Table_1 (Year, Settings_COT, Settings_DOT, Settings_POT, Monitoring_COT, Monitoring_DOT)
VALUES (2025, 'DIYAR [2501-86]', 'Finance [2501-87]', '2501-88 [2]', NULL, NULL);
INSERT INTO Table_1 (Year, Settings_COT, Settings_DOT, Settings_POT, Monitoring_COT, Monitoring_DOT)
VALUES (2025, 'DIYAR [2501-86]', 'Finance [2501-87]', '2501-98 [2]', NULL, NULL);

INSERT INTO Table_2 (Year, PerApp_Pot)
VALUES (2025, '2501-102 [2]');
INSERT INTO Table_2 (Year, PerApp_Pot)
VALUES (2025, '2501-90 [2]');

SELECT * FROM TABLE_1

Year	Settings_COT	Settings_DOT	Settings_POT	Monitoring_COT	Monitoring_DOT
2025	DIYAR [2501-86]	Finance [2501-87]	2501-88 [2]	NULL	NULL
2025	DIYAR [2501-86]	Finance [2501-87]	2501-98 [2]	NULL	NULL

 SELECT * FROM TABLE_2

Year	PerApp_Pot
2025	2501-102 [2]
2025	2501-90 [2]

Join the tables

SELECT
    T1.Year,
    T1.Settings_COT,
    T1.Settings_DOT,
    T1.Settings_POT,
    T1.Monitoring_COT,
    T1.Monitoring_DOT,
    T2.PerApp_Pot
FROM
    Table_1 T1
LEFT JOIN
    Table_2 T2
ON
    T1.Year = T2.Year

Year	Settings_COT	Settings_DOT	Settings_POT	Monitoring_COT	Monitoring_DOT	PerApp_Pot
2025	DIYAR [2501-86]	Finance [2501-87]	2501-88 [2]	NULL	NULL	2501-102 [2]
2025	DIYAR [2501-86]	Finance [2501-87]	2501-88 [2]	NULL	NULL	2501-90 [2]
2025	DIYAR [2501-86]	Finance [2501-87]	2501-98 [2]	NULL	NULL	2501-102 [2]
2025	DIYAR [2501-86]	Finance [2501-87]	2501-98 [2]	NULL	NULL	2501-90 [2]

 To avoid generating 4 rows when joining the tables due to a many-to-many relationship on the Year column, you can create a one-to-one mapping by using the ROW_NUMBER() function.

WITH RankedTable1 AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY Settings_COT) AS RowNum
    FROM Table_1
),
RankedTable2 AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY PerApp_Pot) AS RowNum
    FROM Table_2
)
SELECT
    T1.Year,
    T1.Settings_COT,
    T1.Settings_DOT,
    T1.Settings_POT,
    T1.Monitoring_COT,
    T1.Monitoring_DOT,
    T2.PerApp_Pot
FROM
    RankedTable1 T1
LEFT JOIN
    RankedTable2 T2
ON
    T1.RowNum = T2.RowNum

Result:

Year	Settings_COT	Settings_DOT	Settings_POT	Monitoring_COT	Monitoring_DOT	PerApp_Pot
2025	DIYAR [2501-86]	Finance [2501-87]	2501-88 [2]	NULL	NULL	2501-102 [2]
2025	DIYAR [2501-86]	Finance [2501-87]	2501-98 [2]	NULL	NULL	2501-90 [2]