Combine Fields from Two or More Tables

Last Post 14 Feb 2013 11:47 AM by newbee. 0 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
newbee
New Member
New Member

--
14 Feb 2013 11:47 AM
I have two tables that I need to combine selected fileds from each table into one. Here are codes for Table1, Table2, and desired final table (TableFinal) andmy failed codes!

--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#Table1') IS NOT NULL DROP TABLE #Table1

--===== Create the test table with
CREATE TABLE #Table1
(
ID1 INT,
ID2 INT ,
X1 Varchar(10)
)

--===== Setup any special required conditions especially where dates are concerned

--===== All Inserts into the IDENTITY column


--===== Insert the test data into the test table
INSERT INTO #Table1
(ID1, ID2, X1)
SELECT '1','101', 'A' UNION ALL
SELECT '1','101', 'B' UNION ALL
SELECT '1','101', 'C' UNION ALL
SELECT '1','101', 'D' UNION ALL
SELECT '2','101', '' UNION ALL
SELECT '2','101', '' UNION ALL
SELECT '3','102', 'G' UNION ALL
SELECT '3','102', 'H' UNION ALL
SELECT '3','102', 'I' UNION ALL
SELECT '3','102', 'J' UNION ALL
SELECT '3','103', '' UNION ALL
SELECT '3','103', '' UNION ALL
SELECT '3','103', 'M' UNION ALL
SELECT '3','103', 'N' UNION ALL
SELECT '3','103', 'O'


IF OBJECT_ID('TempDB..#Table2') IS NOT NULL DROP TABLE #Table2

--===== Create the test table with
CREATE TABLE #Table2
(
ID1 INT,
ID2 INT,
X2 INT,
X3 INT )

Select * from #Table1

--===== Insert the test data into the test table
INSERT INTO #Table2
(ID1, ID2, X2, X3)
SELECT '1','101','3' , '200' UNION ALL
SELECT '1','101','4', '' UNION ALL
SELECT '1','101','6', '500' UNION ALL
SELECT '1','101','8' , '600' UNION ALL
SELECT '2','101','' , '100' UNION ALL
SELECT '2','101','2' , '240' UNION ALL
SELECT '3','102','2' , '35' UNION ALL
SELECT '3','102','6' , '' UNION ALL
SELECT '3','102','' , '900' UNION ALL
SELECT '3','102','8' , '600' UNION ALL
SELECT '3','103','10' , '145' UNION ALL
SELECT '3','103','' , '123' UNION ALL
SELECT '3','103','5' , '100' UNION ALL
SELECT '3','103','6' , '550' UNION ALL
SELECT '3','103','1' , '234' UNION ALL
SELECT '4','104','6' , '120' UNION ALL
SELECT '4','104','7' , '350' UNION ALL
SELECT '4','104','8' , '400'

Select * from #Table2


[b]--Desire OutCome------------------[/b]IF OBJECT_ID('TempDB..#TableFinal') IS NOT NULL DROP TABLE #TableFinal

--===== Create the test table with
CREATE TABLE #TableFinal
(
ID1 INT,
ID2 INT ,
X1 INT,
X2 INT,
X3 INT,
)



--===== Insert the test data into the test table
INSERT INTO #TableFinal
(ID1, ID2, X1, X2, X3)
SELECT '1','101', '4','21','1300' UNION ALL
SELECT '2','101', '0','2','340' UNION ALL
SELECT '3','102', '4','16','1535' UNION ALL
SELECT '3','103', '3','22','1152' UNION ALL
SELECT '3','104', '0','21','870'

Select * from #TableFinal

[b]Failed Attempt:[/b]
Select B.ID1,B.ID2,count(distinct A.X1) as X1kount, sum(B.X2) as SumX2, sum(B.X3) as SumX3
from #Table2 B
left outer join #Table1 A on B.ID1=A.ID1 or (B.ID1 IS NULL and A.ID1 IS NULL) and
B.ID2=A.ID2 or (B.ID2 IS NULL and A.ID2 IS NULL)

Group by B.ID1,B.ID2
Order by B.ID1,B.ID2


Thank you for your help,

Helal








Acceptable Use Policy
---