query to add pending after each year

Question

in 2005 cot setting had done for this organization'0B239A36-9F2C-4421-82D2-35F752F52C3D',
in 2006 cot setting had done for this organization '0B239A36-9F2C-4421-82D2-35F752F52C3D',

two more organization whill be there .for that i need to get 'pending' in each year

Amira Bedhiafi · Answer

``` CREATE TABLE Coat_Setting ( Year INT Cot_Setting VARCHAR(50) Org_ID VARCHAR(50) ); INSERT INTO Coat_Setting (Year Cot_Setting Org_ID) VALUES (2025 'DIYAR [2501-166]' '0B239A36-9F2C-4421-82D2-35F752F52C3D') (2026 'DIYAR [2501-181]' '0B239A36-9F2C-4421-82D2-35F752F52C3D'); CREATE TABLE Not_Initiation_Organization ( Year INT Org_ID VARCHAR(50) ); INSERT INTO Not_Initiation_Organization (Year Org_ID) VALUES (2025 'F58F8899-8A3C-4ABC-ABA8-43A0619C041C') (2025 'BE0E501C-FF01-4B18-99A8-4CE1664C0F22') (2026 'F58F8899-8A3C-4ABC-ABA8-43A0619C041C') (2026 'BE0E501C-FF01-4B18-99A8-4CE1664C0F22'); ``` ``` status 6 rows affected ``` ``` WITH Years AS ( SELECT DISTINCT Year FROM Coat_Setting ) Organizations AS ( SELECT DISTINCT Org_ID FROM Coat_Setting UNION SELECT DISTINCT Org_ID FROM Not_Initiation_Organization ) All_Combinations AS ( SELECT y.Year o.Org_ID FROM Years y CROSS JOIN Organizations o ) Final_Result AS ( SELECT ac.Year COALESCE(cs.Cot_Setting 'Pending') AS Cot_Setting ac.Org_ID FROM All_Combinations ac LEFT JOIN Coat_Setting cs ON ac.Year = cs.Year AND ac.Org_ID = cs.Org_ID ) SELECT * FROM Final_Result ORDER BY Year Org_ID; ``` | Year | Cot\_Setting | Org\_ID | | ----:|:-----------|:------| | 2025 | DIYAR \[2501-166] | 0B239A36-9F2C-4421-82D2-35F752F52C3D | | 2025 | Pending | BE0E501C-FF01-4B18-99A8-4CE1664C0F22 | | 2025 | Pending | F58F8899-8A3C-4ABC-ABA8-43A0619C041C | | 2026 | DIYAR \[2501-181] | 0B239A36-9F2C-4421-82D2-35F752F52C3D | | 2026 | Pending | BE0E501C-FF01-4B18-99A8-4CE1664C0F22 | | 2026 | Pending | F58F8899-8A3C-4ABC-ABA8-43A0619C041C | [fiddle](https://dbfiddle.uk/Mg4_agjH)