Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Join two columns in one

Author  Topic 

nikoz
Yak Posting Veteran

63 Posts

Posted - 2013-03-03 : 14:18:14
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!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-03-03 : 15:10:46
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

63 Posts

Posted - 2013-03-03 : 15:21:51
The columns is from diffrent tables. i forgot to mentioned
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-03 : 17:09:45
[code]select column1 as column3 from Table1
union all
select column2 from Table2[/code]
Go to Top of Page

nikoz
Yak Posting Veteran

63 Posts

Posted - 2013-03-03 : 17:25:08
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-03 : 17:29:44
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.
Go to Top of Page

nikoz
Yak Posting Veteran

63 Posts

Posted - 2013-03-03 : 17:35:15
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-03 : 17:40:41
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

63 Posts

Posted - 2013-03-03 : 17:42:43
and how many columns i gonna have now?
Go to Top of Page

nikoz
Yak Posting Veteran

63 Posts

Posted - 2013-03-03 : 17:52:43
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



Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-03 : 20:52:25
[code]SELECT
NAME,
LastName,
adress,
NULL AS PostOfficeNumber
FROM
Table1
UNION ALL
SELECT
NAME,
lastName,
NULL AS adress,
PostOfficeNumber
FROM
Table2[/code]
Go to Top of Page

nikoz
Yak Posting Veteran

63 Posts

Posted - 2013-03-04 : 00:57:50
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
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-03-04 : 06:39:10
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

63 Posts

Posted - 2013-03-04 : 07:21:27
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
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-03-04 : 07:41:17
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

63 Posts

Posted - 2013-03-04 : 14:06:20
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
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-03-05 : 06:05:46
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

63 Posts

Posted - 2013-03-06 : 14:30:18
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
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-03-07 : 06:25:07
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
   

- Advertisement -