Merge two tables, one column each into one table with two columns

Question

Merge two tables, one column each into one table with two columns

lionfan91

SSCrazy Eights

Points: 8794
More actions
February 18, 2009 at 3:47 pm

#75419

I'm sure this is straightforward but it's the end of the day, my brain is fried, and I'm coming up empty. 🙂
I have two tables each with the same number of rows. Table A has Column A with values "A", "B", "C". Table B has Column B with values 1, 2, 3.
I want Table C with columns A and B and three rows with values "A|1", "B|2", "C|3".
Thoughts?
Thanks in advance... Steve

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

psuedocode sucks....when you abstract the real problem, you get crappy answers, that you end up having to explain lots of times till you come around to teh real answer. Show us real world data and examples.

since you didn't say the tables are related, you'll end up with a Cartesian product...A|1,A|2. and A|3 would be part of the set...so I'm going to assume the tables really are related.

--Table A has Column A with values "A", "B", "C".

create table #Letters(letterId int identity(1,1) ,Letter varchar(30))

--Table B has Column B with values 1, 2, 3.

create table #Numbers(numberId int identity(1,1) ,Number int)

insert into #Letters select 'A' union all select 'B' union all select 'C'

insert into #Numbers select 1 union all select 2 union all select 3

select #Letters.Letter + '|' + convert(varchar,#Numbers.number)

from #Letters

inner join #Numbers on #Letters.letterid = #Numbers.numberid

results:

A|1

B|2

C|3

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 2

milzs (2/18/2009)
I'm sure this is straightforward but it's the end of the day, my brain is fried, and I'm coming up empty. 🙂
I have two tables each with the same number of rows. Table A has Column A with values "A", "B", "C". Table B has Column B with values 1, 2, 3.
I want Table C with columns A and B and three rows with values "A|1", "B|2", "C|3".
Thoughts?
Thanks in advance... Steve

Where do you want to show the data?

Madhivanan

Failing to plan is Planning to fail

lionfan91 SSCrazy Eights Points: 8794 More actions · Answer 3

I'm working on a classified network, so it's kind of hard to show real world examples. I tried to give a simple example that represented my problem...

What I actually have is two comma-delimited strings, one that contains GUIDs that represent specific entities and the other that contains version numbers of those entities. I have a UDF that converts the strings to two tables and I needed to "join" those tables so that the resulting table had one column with the GUIDs and one with the version IDs.

All this said, thanks for your reply -- with some minor modifications I've got it working like a champ...

Steve

Sergiy SSC Guru Points: 110208 More actions · Answer 4

Only thing you need is to make sure your UDF returns sequence number in line with each value.

Then you're OK.

Otherwise you need to create another UDF which will return sequence number.

Using IDENTITY in temp tables sometimes may not work.

_____________
Code for TallyGenerator