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

avatar image

gjl15
49 4 4 7

(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

avatar image

John Franklin
414 1 3 7

(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

avatar image

Grant Fritchey ♦♦
137k 20 43 81

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

avatar image

TimothyAWiseman
15.6k 22 51 38

(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

avatar 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

avatar 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.

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:

x150

asked: Apr 07, 2010 at 02:09 PM

Seen: 61633 times

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

Copyright 2016 Redgate Software. Privacy Policy