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 thiscolumn1 Johnny Bob column2 Michael Joe Column3 Johnny Bob Michael JoeAnd 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 yourTableEveryday I learn something that somebody else already knew |
|
|
nikoz
Yak Posting Veteran
63 Posts |
Posted - 2013-03-03 : 15:21:51
|
The columns is from diffrent tables. i forgot to mentioned |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-03 : 17:09:45
|
[code]select column1 as column3 from Table1union all select column2 from Table2[/code] |
|
|
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? |
|
|
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. |
|
|
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? |
|
|
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 Table1select column1, column2, cast('' as varchar(32)) as column3, cast('' as varchar(256)) as column4from Table1union all select columnA, null, columnC columnDfrom Table2 |
|
|
nikoz
Yak Posting Veteran
63 Posts |
Posted - 2013-03-03 : 17:42:43
|
and how many columns i gonna have now? |
|
|
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.Tabela1Column(name) JonhBobColumn(LastName)AdamsParkerColumn(adress)SomeStreet1SomeStreet2Table2Column(name)MichaelJamesColumn(lastName)SmithDeereColumn(PostOfficeNumber)1130011000and i want thisColumn(name) JonhBobMichaleJamesColumn(LastName)AdamsParkerSmithDeereColumn(adress)SomeStreet1SomeStreet2NULLNULLColumn(PostOfficeNumber)NULLNULL1130011000 |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-03 : 20:52:25
|
[code]SELECT NAME, LastName, adress, NULL AS PostOfficeNumberFROM Table1UNION ALLSELECT NAME, lastName, NULL AS adress, PostOfficeNumberFROM Table2[/code] |
|
|
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. |
|
|
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 table2better if you read and understand the machanism of UNION operator http://msdn.microsoft.com/en-us/library/ms180026.aspxCheersMIK |
|
|
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. |
|
|
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.CheersMIK |
|
|
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 thisTable1Column(name) JonhBobAdamNickColumn(LastName)AdamsParkerCourtneyGrayColumn(address)5th Street21th Street10th StreetWashington StreetColumn(PostOfficeNumber)11300110001230021000Column(E-mail)SomeE-mail1SomeE-mail2SomeE-mail3TABLE2Column(name)MichaelJamesColumn(lastName)SmithDeereColumn(PostOfficeNumber)1130011000TABLE3Column(name)MikyRobertAxelColumn(address)101th street62th street90th streetColumn(City)LondonNULLL.AAND I WANT RESAULT LIKE THISColumn(name)JonhBobAdamNickMichael James Miky RobertAxel Column(LastName)AdamsParkerCourtnyGraySmithDeereNULLNULLNULLColumn(Address)5th Street21th Street10th StreetWashington StreetNULLNULL101th street62th street90th streetColumn(PostOfficeNumber)113001100012300210001130011000NULLNULLNULLColumn(E-mail)SomeE-mail1SomeE-mail2SomeE-mail3NULLNULLNULLNULLNULLNULLColumn (City)NULLNULLNULLNULLNULLNULLLondonNULLL.A |
|
|
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 Table1UNION ALLSELECT Name,LastName,NULL,PostOfficeNumber,NULL,NULL FROM table2UNION ALLselect Name,NULL,Address,NULL,NULL,City from table3drop table table1,table2,table3CheersMIK |
|
|
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.... |
|
|
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!CheersMIK |
|
|
|