Combining 2 different datasets

Question

Post reply

Combining 2 different datasets

Trybbe

Hall of Fame

Points: 3967

January 25, 2017 at 4:30 am

#401480

Hi combine 2 datasets that have different column names, different data and different number of columns into 1 output.

see example below.

Table 1

Unit1	Unit2	Unit3	Unit4
324	176	999	786
123	654	98	23


Table 2

Name	Lastname	Residential	Surburb	City	Zip/Code
Dave	Johnson	Address1	Address2	Address3	Postal code
Mutton	Murphy	Address1	Address2	Address3	Postal code


Desired output

324	176	999	786
123	654	98	23
Dave	Johnson	Address1	Address2	Address3	Postal code
Mutton	Murphy	Address1	Address2	Address3	Postal code

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

I can't think why you'd want to do this, but assuming there's a good reason, there are two things you need to do. First, make the number of columns in each table the same, which means adding NULL or blank columns to the SELECT list for the table with fewer columns. Second, make sure the data types are the same. You haven't provided table DDL, but less assume that the data types of all columns in the first table is int, and for the second table it's varchar(20). Then you need to convert all the columns in the first table to varchar(20). Once you've done that, you can combine the two result sets with a UNION operator (or UNION ALL if you're sure there will be no duplication between the tables).

John

Trybbe Hall of Fame Points: 3967 More actions · Answer 2

Thanks John, I was hoping that there is an alternative approach but it looks like I will have to follow your process.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Trybbe - Wednesday, January 25, 2017 5:30 AM
Thanks John, I was hoping that there is an alternative approach but it looks like I will have to follow your process.

OK, good luck. Really this is the type of thing that should be done in your presentation layer, rather as you did in your Excel workbook. You can also use Reporting Services. There are all sorts of problems you can run into when converting data types, if you're not careful.

John