SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Join two columns in one
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nikoz
Yak Posting Veteran

57 Posts

Posted - 03/03/2013 :  14:18:14  Show Profile  Reply with Quote
How can I join column1 and colum2 into colunm3 like this

column1

Johnny
Bob

column2

Michael
Joe

Column3

Johnny
Bob
Michael
Joe

And the columns is from diffrent tables!

Edited by - nikoz on 03/03/2013 15:20:40

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 03/03/2013 :  15:10:46  Show Profile  Reply with Quote
SELECT LTRIM(REPLACE(ISNULL(Column1,' ') + ISNULL(Column2,' ') + ISNULL(Column3,' '),' ',' '))


FROM yourTable

Everyday I learn something that somebody else already knew
Go to Top of Page

nikoz
Yak Posting Veteran

57 Posts

Posted - 03/03/2013 :  15:21:51  Show Profile  Reply with Quote
The columns is from diffrent tables. i forgot to mentioned
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3565 Posts

Posted - 03/03/2013 :  17:09:45  Show Profile  Reply with Quote
select column1 as column3 from Table1
union all 
select column2 from Table2
Go to Top of Page

nikoz
Yak Posting Veteran

57 Posts

Posted - 03/03/2013 :  17:25:08  Show Profile  Reply with Quote
Is it posible to use UNION ALL when you don have same number of columns and how to do that?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3565 Posts

Posted - 03/03/2013 :  17:29:44  Show Profile  Reply with Quote
You have to have the same number of columns. You can use workarounds such as using place holders etc., but in the end, there has to be the same number of columns in each table and corresponding columns in each table have to be of compatible data type.

Edited by - James K on 03/03/2013 17:30:06
Go to Top of Page

nikoz
Yak Posting Veteran

57 Posts

Posted - 03/03/2013 :  17:35:15  Show Profile  Reply with Quote
How to uninon two tables when I don't have a samw number of columns. I want results like union?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3565 Posts

Posted - 03/03/2013 :  17:40:41  Show Profile  Reply with Quote
Something like in the example below, where Table1 has only two columns and TAble2 has 3 columns, of which only ColumnA corresponds to column1 of Table1
select
	column1,
	column2,
	cast('' as varchar(32)) as column3,
	cast('' as varchar(256)) as column4
from
	Table1
union all 
select
	columnA,
	null,
	columnC
	columnD
from
	Table2
Go to Top of Page

nikoz
Yak Posting Veteran

57 Posts

Posted - 03/03/2013 :  17:42:43  Show Profile  Reply with Quote
and how many columns i gonna have now?
Go to Top of Page

nikoz
Yak Posting Veteran

57 Posts

Posted - 03/03/2013 :  17:52:43  Show Profile  Reply with Quote
I want to join table like this. This is example of tables.

Tabela1

Column(name)
Jonh
Bob

Column(LastName)

Adams
Parker

Column(adress)

SomeStreet1
SomeStreet2

Table2

Column(name)

Michael
James

Column(lastName)

Smith
Deere

Column(PostOfficeNumber)

11300
11000


and i want this

Column(name)
Jonh
Bob
Michale
James

Column(LastName)

Adams
Parker
Smith
Deere

Column(adress)

SomeStreet1
SomeStreet2
NULL
NULL

Column(PostOfficeNumber)
NULL
NULL
11300
11000




Edited by - nikoz on 03/03/2013 18:04:07
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3565 Posts

Posted - 03/03/2013 :  20:52:25  Show Profile  Reply with Quote
SELECT
	NAME,
	LastName,
	adress,
	NULL AS PostOfficeNumber
FROM
	Table1
UNION ALL
SELECT
	NAME,
	lastName,
	NULL AS adress,
	PostOfficeNumber
FROM
	Table2
Go to Top of Page

nikoz
Yak Posting Veteran

57 Posts

Posted - 03/04/2013 :  00:57:50  Show Profile  Reply with Quote
How to do that when I have a different nubers of columns? Like in table1 i have 4 columns and in table2 I have 3 columns.
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 03/04/2013 :  06:39:10  Show Profile  Reply with Quote
if table 2 has all the three columns in Table1 then use NULL as the fourth column in the SELECT statement of table2

better if you read and understand the machanism of UNION operator http://msdn.microsoft.com/en-us/library/ms180026.aspx

Cheers
MIK
Go to Top of Page

nikoz
Yak Posting Veteran

57 Posts

Posted - 03/04/2013 :  07:21:27  Show Profile  Reply with Quote
Well for example I want 2 of 3 columns in table2 is same like in table2. If table1 have 4 columns then the result should be 6.
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 03/04/2013 :  07:41:17  Show Profile  Reply with Quote
Well .. what you are actually trying to accomplish? So far we were thinking that you're trying to append the dataset below each other ..but with this last response (the result should be 6), shows that you want to join the data set. I think you've other post where Visakh has shown you the way how to do it.

Cheers
MIK
Go to Top of Page

nikoz
Yak Posting Veteran

57 Posts

Posted - 03/04/2013 :  14:06:20  Show Profile  Reply with Quote
My situation is like this. I have 3 tables and they look like this

Table1

Column(name)
Jonh
Bob
Adam
Nick

Column(LastName)

Adams
Parker
Courtney
Gray


Column(address)

5th Street
21th Street
10th Street
Washington Street

Column(PostOfficeNumber)

11300
11000
12300
21000

Column(E-mail)

SomeE-mail1
SomeE-mail2
SomeE-mail3

TABLE2

Column(name)

Michael
James

Column(lastName)

Smith
Deere

Column(PostOfficeNumber)

11300
11000

TABLE3

Column(name)

Miky
Robert
Axel

Column(address)

101th street
62th street
90th street

Column(City)
London
NULL
L.A

AND I WANT RESAULT LIKE THIS

Column(name)

Jonh
Bob
Adam
Nick
Michael
James
Miky
Robert
Axel

Column(LastName)

Adams
Parker
Courtny
Gray
Smith
Deere
NULL
NULL
NULL

Column(Address)

5th Street
21th Street
10th Street
Washington Street
NULL
NULL
101th street
62th street
90th street

Column(PostOfficeNumber)

11300
11000
12300
21000
11300
11000
NULL
NULL
NULL

Column(E-mail)

SomeE-mail1
SomeE-mail2
SomeE-mail3
NULL
NULL
NULL
NULL
NULL
NULL

Column (City)

NULL
NULL
NULL
NULL
NULL
NULL
London
NULL
L.A


Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 03/05/2013 :  06:05:46  Show Profile  Reply with Quote
Always do provide sample data in form of insert so that its is easier for us to help you quickly.

Any way below is how you can accomplish it

Create TAble Table1 (Name Varchar(20),LastName Varchar(20),Address varchar(30),PostOfficeNumber int,Email varchar(20))
INSERT INTO Table1 VALUES
('Jonh','Adams','5th Street',11300,'SomeE-mail1'),
('Bob','Parker','21th Street',11000,'SomeE-mail2'),
('Adam','Courtney','10th Street',12300,'SomeE-mail3'),
('Nick','Gray','Washington Street',21000,NULL)

Create Table table2(Name varchar(10),LastName varchar(10),PostOfficeNumber int)
INSERT INTO Table2 VALUES
('Michael','Smith',11300),
('James','Deere',11000)

create TAble table3(Name varchar(10),Address varchar(20),City varchar(10))
Insert into Table3 VALUES
('Miky','101th street','London'),
('Robert','62th street',NULL),
('Axel','90th street','L.A')

SELECT Name,LastName,address,PostOfficeNumber,email,NULL FROM Table1
UNION ALL
SELECT Name,LastName,NULL,PostOfficeNumber,NULL,NULL FROM table2
UNION ALL
select Name,NULL,Address,NULL,NULL,City from table3
drop table table1,table2,table3



Cheers
MIK
Go to Top of Page

nikoz
Yak Posting Veteran

57 Posts

Posted - 03/06/2013 :  14:30:18  Show Profile  Reply with Quote
Sorry for that I am new in all this :) Also I solve the problem little bit different... but same principle....
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 03/07/2013 :  06:25:07  Show Profile  Reply with Quote
Glad to help you.. but in future do explain the problem in light of the sample data .. even if it takes a page to explain .. that helps the team to understand. Have fun!

Cheers
MIK
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000