<pre lang="c#">;WITH K_RT_MasterRetailStoresTable (CommonId,Branch) AS
(
SELECT ROW_NUMBER() OVER(ORDER BY Branch) AS CommonId,Branch
FROM K_RT_MasterRetailStores
)
,K_BR_AddBranchTable (CommonId, branch) AS
(
SELECT ROW_NUMBER() OVER(ORDER BY branch) AS CommonId,branch
FROM K_BR_AddBranch
)
,K_HM_BranchTable (CommonID,branch) as
(
SELECT ROW_NUMBER() OVER(ORDER BY branch) as CommonId,branch
FROM K_HM_Branch
)
,k_Master_BranchesTable (CommonID, branch) as
(
SELECT ROW_NUMBER() OVER(ORDER BY BRANCH) AS COMMONID,BRANCH
FROM k_Master_Branches
)
SELECT distinct BRANCH FROM K_RT_MasterRetailStoresTable
UNION ALL
SELECT distinct BRANCH FROM K_BR_AddBranchTable
UNION ALL
SELECT distinct BRANCH FROM K_HM_BranchTable
UNION ALL
SELECT distinct BRANCH FROM k_Master_BranchesTable;