How to combine more than one Table with out common column names?

Question

4.00/5 (1 vote)

See more:

SQL

;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 MRS.BRANCH,AB.BRANCH,B.BRANCH,MB.BRANCH
 FROM K_RT_MasterRetailStoresTable MRS
 LEFT OUTER JOIN K_BR_AddBranchTable AB ON MRS.COMMONID = AB.COMMONID
 LEFT OUTER JOIN K_HM_BranchTable B ON AB.COMMONID = B.COMMONID
 LEFT OUTER JOIN k_Master_BranchesTable MB ON B.COMMONID = MB.COMMONID

Now it shows 4 columns ,4 tables branches are displyed..Now i want to display in single column ALL branch...what i do ? please help me?

Posted 5-Feb-14 22:53pm

Siva Hyderabad

Add a Solution

2 solutions

Solution 1

Try like this..

SQL

SELECT BRANCH1 AS BRANCH FROM BRANCH_TABLE
 UNION ALL
 SELECT BRANCH2 AS BRANCH FROM BRANCH_TABLE
 UNION ALL
 SELECT BRANCH3 AS BRANCH FROM BRANCH_TABLE
 UNION ALL
 SELECT BRANCH4 AS BRANCH FROM BRANCH_TABLE;

Posted 5-Feb-14 23:24pm

s#@!k

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Siva Hyderabad · Accepted Answer · 2014-02-05T23:46:00

SQL

<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;