February 14, 2013 at 1:28 pm
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 ,
X1Varchar(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,
X2INT,
X3INT )
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
--Desire OutCome------------------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,
X3INT,
)
--===== 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
Failed Attempt:
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
February 14, 2013 at 2:30 pm
Nice job posting ddl and sample data. I am not quite sure what the business rules are but this is maybe something to get you started. It doesn't quite match up all the totals.
select ID1, ID2, count(*),
(select sum(X2) from #Table2 t2 where t2.ID1 = t1.ID1 and t2.ID2 = t2.ID2),
(select sum(X3) from #Table2 t2 where t2.ID1 = t1.ID1 and t2.ID2 = t2.ID2)
from #Table1 t1
group by ID1, ID2
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 14, 2013 at 3:16 pm
Thank you for yuor quick response...but as you indicated, figures don't match up and tha's what I need
Helal
February 14, 2013 at 7:50 pm
Here you go:
SELECT
b.ID1
,b.ID2
,(SELECT COUNT(DISTINCT X1) FROM #Table1 WHERE ID1=b.ID1 AND ID2=b.ID2 AND NULLIF(X1,'') IS NOT NULL) AS X1Count
,(SELECT SUM(X2) FROM #Table2 WHERE ID1=b.ID1 AND ID2=b.ID2) AS SumX2
,(SELECT SUM(X3) FROM #Table2 WHERE ID1=b.ID1 AND ID2=b.ID2) AS SumX3
FROM
#Table2 AS b
LEFT JOIN #Table1 AS a
ON b.ID1 = a.ID1
AND b.ID2 = a.ID2
GROUP BY
b.ID1,b.ID2
ORDER BY
b.ID1,b.ID2
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply