x

Combine columns from multiple tables into one

Sorry if this seems simple. Either I'm not asking the right question, or understanding the answers I've found so far.
I have 12 temp tables, each created from a query. Every table has a column named 'ComputerName', and a second column with that query's results. Only 'ComputerName's are listed in each temp table where there was a result of a query.
(Ex. tab1 has pc1 - tab1.data, pc3 - tab1.data, and pc5 - tab1.data. tab2 has pc1 - tab2.data, pc2 - tab2.data, and pc6 - tab2.data.)
I want to combine the tables into one table.
(Ex: Result would be:
pc1 - tab1.data - tab2.data,
pc2 - null - tab2.data,
pc3 - tab1.data - null,
pc5 - tab1.data - null,
pc6 - null - tab2.data .)
I realize I may need to start with putting two tables together, and then continue that process with the remaining 10. But I need a starting point. Any thoughts? Thanks.

more ▼

asked Apr 07, 2010 at 02:09 PM in Default

gjl15 gravatar image

gjl15
49 3 4 5

(comments are locked)
10|1200 characters needed characters left

6 answers: sort voted first

Grant beat me to it but I believe you are simply wanting to LEFT JOIN all of your tables ...


SELECT T1.ComputerName, T1.Data, T2.Data, T3.Data, T4.Data
FROM #TMP1 T1
LEFT JOIN #TMP2 T2
 ON T1.ComputerName = T2.ComputerName
LEFT JOIN #TMP3 T3
 ON T1.ComputerName = T3.ComputerName
LEFT JOIN #TMP4 T4
 ON T1.ComputerName = T4.ComputerName

... will result in the following output ...


ComputerName     Data             Data             Data             Data
---------------- ---------------- ---------------- ---------------- ----------------
PC1              AAA              111              NULL             aaa
PC2              BBB              NULL             ###              bbb
PC3              CCC              222              NULL             ccc
PC4              DDD              NULL             $$$              ddd
PC5              EEE              333              NULL             eee

If your initial table does not have all the computer names in it you could either get a distinct list on the fly (as in the example below), create another table that has all the computer names and use it as your primary SELECT FROM table, or possible create a SQL View that is the equivalent to my "on the fly method".

 SELECT C.ComputerName, T1.Data, T2.Data, T3.Data, T4.Data FROM ( SELECT DISTINCT ComputerName FROM #TMP1  UNION  SELECT DISTINCT ComputerName FROM #TMP2  UNION  SELECT DISTINCT ComputerName FROM #TMP3  UNION  SELECT DISTINCT ComputerName FROM #TMP4 ) C
LEFT JOIN #TMP1 T1 ON C.ComputerName = T1.ComputerName LEFT JOIN #TMP2 T2 ON C.ComputerName = T2.ComputerName LEFT JOIN #TMP3 T3 ON C.ComputerName = T3.ComputerName LEFT JOIN #TMP4 T4 ON C.ComputerName = T4.ComputerName
more ▼

answered Apr 07, 2010 at 02:36 PM

John Franklin gravatar image

John Franklin
414 1 1 3

Thanks John. That's really close, but T2.ComputerName will only show up if there is a match to T1.ComputerName. I want to join T1, T2, T3, etc, on ComputerName if there is a match. But if there is no match of ComputerName, I still need to get that data in the resulting table. So, adding to your example above, PC6 could be null, null, null, FFF. Meaning the first time PC6 appears would be coming from T5 (no previous matches in T's 1-4).
Apr 07, 2010 at 02:51 PM gjl15

See my additional example based on your latest post about T1 not having all of the ComputerNames in it. I believe that should take care of your problem.

~ John
Apr 07, 2010 at 03:13 PM John Franklin
THAT IS IT !!!!! THANKS JOHN !!!! I just did this against 4 of the temp tables and 1200 rows just shot out, 5 columns wide. Thank you very much!
Apr 07, 2010 at 03:31 PM gjl15
I just realized myself before your post came on, that I needed to UNION the [ComputerName]s from all the table first. Then I could join each temp table's distinct collection of [ComputerName]s against a distinct master list. Thanks again! You saved me from my plan B, which was very ugly.
Apr 07, 2010 at 03:35 PM gjl15
Nice job John. I didn't understand what we were shooting for well enough.
Apr 07, 2010 at 03:37 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

I'm a little confused by what you mean you say "put these tables together" but I think you're looking for one of two things. Either, you want to combine the results of all the tables, all at once, in which case you need to use the UNION or UNION ALL operator like this:

SELECT *
FROM tab1
UNION ALL
SELECT *
FROM Tab2
--etc.

This will put all these tables into a single result set. In this case I used UNION ALL, which just does a straight combination. If you just put in UNION, then you get a unique set of data, eliminating duplicates.

Or, you're trying to combine all the tables to get common data out of them, something like this:

SELECT *
FROM Tab1
JOIN Tab2
ON Tab1.ComputerName = Tab2.ComputerName

But I'm not sure that's what you mean.

more ▼

answered Apr 07, 2010 at 02:29 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
100k 19 21 74

Thanks Grant. Your second suggestion is close. I've tried that, but but with a Left or Full Outer Join. What I get is if Tab2.ComputerName is not in Tab1, Tab2.ComputerName with Tab2.data will not show up in the result. I think I need an Outer Join with a Union but I haven't been able to figure it out.
Apr 07, 2010 at 02:41 PM gjl15
Clearly, John has it right. Sorry I didn't get what you were driving towards.
Apr 07, 2010 at 03:38 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

From what I gather, you should just be able to change the join to a full join in Grant's example (expanded to include all of your tables of course.). This article on MSDN may help: http://msdn.microsoft.com/en-us/library/ms187518.aspx

more ▼

answered Apr 07, 2010 at 03:19 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

(comments are locked)
10|1200 characters needed characters left
update Table1 Set (Select Column1 From Table1 Where ColumnID1=Table1.ColumnID1)='11', (Select Column2 From Table2 Where ColumnID2=Table1.ColumnID1)='22' Where Table1.ColumnId1=1
more ▼

answered Apr 25, 2012 at 06:01 PM

sumitcl gravatar image

sumitcl
0

(comments are locked)
10|1200 characters needed characters left
update Table1 Set (Select Column1 From Table1 Where ColumnID1=Table1.ColumnID1)='11', (Select Column2 From Table2 Where ColumnID2=Table1.ColumnID1)='22' Where Table1.ColumnId1=1
more ▼

answered Apr 25, 2012 at 06:01 PM

sumitcl gravatar image

sumitcl
0

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x117

asked: Apr 07, 2010 at 02:09 PM

Seen: 46032 times

Last Updated: Jun 04, 2012 at 06:04 AM