Output in a Single Table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Output in a Single Table

Author

Topic

sqldba20
Posting Yak Master

183 Posts

Posted - 2007-11-05 : 12:25:57

Folks:

I need help with a script. I have 4 tables with AsOfDate, Country and FactorInt columns. In each table AsOfdate and Country are the same for that date but the FACTORINT column varies. I want the output in a single table (master table) and it should be in this format and look like this:

TBLMASTER
AsOfDate Country Factor1 Factor2 Factor3 Factor4
------------------------------------------------------------------------------------------
2007-10-29 Brazil 5 4 2 3
2007-10-30 Brazil 2 2 6 2
2007-10-31 Brazil 6 8 9 0
2007-10-29 Canada 8 9 8 8
2007-10-30 Canada 1 5 3 4
2007-10-31 Canada 3 1 5 7
2007-10-29 USA 9 3 4 6
2007-10-30 USA 3 6 0 2
2007-10-31 USA 7 3 1 1

Each child table has data for 3 days for each country with different FactorInt, with total of 9 rows in each table. By clubbing it in a single table I should have only 9 rows. I ran the below script to get it into single table but for some reason it doesn't work and happen to give around 23 rows and some weird data.

insert into TBLMASTER (AsOfDate,Country,Factor1,Factor2,Factor3,Factor4)
select a.AsOfDate,a.Country,a.FactorInt,b.FactorInt,c.FactorInt,d.FactorInt
from tbl1 a
inner join tbl2 b
on a.AsOfDate = b.AsOfDate
and a.Country = b.Country
inner join tbl3 c
on b.AsOfDate = c.AsOfDate
and b.Country = c.Country
inner join tbl4 d
on c.AsOfDate = d.AsOfDate
and c.Country = d.Country

==================================================================================================

TBL1
AsOfDate Country FactorInt
-------------------------------------------
10/31/2007 Canada 3
10/30/2007 Brazil 2
10/29/2007 USA 9
10/30/2007 Canada 1
10/29/2007 Brazil 5
10/31/2007 USA 7
10/29/2007 Canada 8
10/31/2007 Brazil 6
10/30/2007 USA 3

TBL2
AsOfDate Country FactorInt
-------------------------------------------
10/31/2007 Canada 1
10/30/2007 Brazil 2
10/29/2007 USA 3
10/30/2007 Canada 5
10/29/2007 Brazil 4
10/31/2007 USA 3
10/29/2007 Canada 9
10/31/2007 Brazil 8
10/30/2007 USA 6

TBL3
AsOfDate Country FactorInt
-------------------------------------------
10/31/2007 Canada 5
10/30/2007 Brazil 6
10/29/2007 USA 4
10/30/2007 Canada 3
10/29/2007 Brazil 2
10/31/2007 USA 1
10/29/2007 Canada 8
10/31/2007 Brazil 9
10/30/2007 USA 0

TBL4
AsOfDate Country FactorInt
-------------------------------------------
10/31/2007 Canada 7
10/30/2007 Brazil 2
10/29/2007 USA 6
10/30/2007 Canada 4
10/29/2007 Brazil 3
10/31/2007 USA 1
10/29/2007 Canada 8
10/31/2007 Brazil 0
10/30/2007 USA 2

All these output should get stored in a single table (tblmaster) with the following format and the output should like this
==================================================================================================================

TBLMASTER
AsOfDate Country Factor1 Factor2 Factor3 Factor4
------------------------------------------------------------------------------------------
2007-10-29 Brazil 5 4 2 3
2007-10-30 Brazil 2 2 6 2
2007-10-31 Brazil 6 8 9 0
2007-10-29 Canada 8 9 8 8
2007-10-30 Canada 1 5 3 4
2007-10-31 Canada 3 1 5 7
2007-10-29 USA 9 3 4 6
2007-10-30 USA 3 6 0 2
2007-10-31 USA 7 3 1 1

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-11-05 : 12:30:57

perhaps you should normalize the data first. That would make it MUCH easier

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

sqldba20
Posting Yak Master

183 Posts

Posted - 2007-11-05 : 12:32:14

Well, I have the data in that format now. Is there a way I could do it with a script or stored procedure?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-05 : 13:13:48

You get duplicate rows because there is same dateOf in several tables.
Try DISTINCT.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources