Combining 2 different as one. - 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 2000 Forums

SQL Server Development (2000)

Combining 2 different as one.

Author

Topic

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2006-07-20 : 08:47:37

I have 2 table tblDemo1 and tblDemo2 and both have different fields.

Is it possible to combine these 2 table as one cause i want to do a pivot table instead of 2

tblDemo1

ClientNo Amount 2006001 2006002 2006003
----------- ----------- ----------- ----------- -----------
2001 1000 1000 0 0
2002 1200 1200 0 0
2003 1300 1300 0 0
2004 1200 0 1200 0
2005 2600 0 2600 0
2006 9800 0 9800 0
2007 3540 0 3540 0
2008 1200 0 0 1200
2009 2200 0 0 2200

tblDemo2

ClientNo Amount FC-2006001 FC-2006002 FC-2006003
----------- ----------- ----------- ----------- -----------
2001 1000 1000 0 0
2001 1200 1200 0 0
2003 1300 1300 0 0
2003 1200 0 1200 0
2005 2600 0 2600 0
2006 9800 0 9800 0
2007 3540 0 3540 0
2007 1200 0 0 1200
2009 2200 0 0 2200
2009 3982 0 0 3982
2009 1200 0 0 1200

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-20 : 08:50:59

How do you want to combine it ?

humanpuck
Yak Posting Veteran

94 Posts

Posted - 2006-07-20 : 08:54:53

It sounds like all you want to do is a simple inner join. Create a view, inner join the tbls on ClientNo and you've essentially combined the tbls when run a select against your view.

Create View dbo.ClientCombine as

select a.clientno,a.Amount, 2006001, 2006002, 2006003,
FC-2006001, FC-2006002, FC-2006003
from tblDemo1 a inner join tblDemo2 b on
a.clientno = b.clientno and
a.amount = b.amount

select * from dbo.ClientCombine

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2006-07-20 : 09:06:05

Combine into as:

ClientNo Amount 2006001 2006002 2006003 FC-2006001 FC-2006002 FC-2006003
----------- ----------- ----------- ----------- -------------------------------------------------------
2001 1000 1000 0 0 0 0 0
2002 1200 1200 0 0 0 0 0
2003 1300 1300 0 0 0 0 0
2004 1200 0 1200 0 0 0 0
2005 2600 0 2600 0 0 0 0
2006 9800 0 9800 0 0 0 0
2007 3540 0 3540 0 0 0 0
2008 1200 0 0 1200 0 0 0
2009 2200 0 0 2200 0 0 0
2010 3982 0 0 3982 0 0 0
2011 1200 0 0 1200 0 0 0
2001 1000 0 0 0 1000 0 0
2001 1200 0 0 0 1200 0 0
2003 1300 0 0 0 1300 0 0
2003 1200 0 0 0 0 1200 0
2005 2600 0 0 0 0 2600 0
2006 9800 0 0 0 0 9800 0
2007 3540 0 0 0 0 3540 0
2007 1200 0 0 0 0 0 1200
2009 2200 0 0 0 0 0 2200
2010 3982 0 0 0 0 0 3982
2010 1200 0 0 0 0 0 1200

Sorry about the layout. Thanks

humanpuck
Yak Posting Veteran

94 Posts

Posted - 2006-07-20 : 09:08:39

did you try what I posted above?

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2006-07-20 : 09:12:13

so sorry please disregard my post cause humanpuck has given me an ideal. i am not sure it work cause it is not a one to many relation. Let me sort it out first and then i come back here again.

thanks you so much

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-20 : 09:16:37

quote:
Originally posted by sg2255551

Combine into as:

ClientNo Amount 2006001 2006002 2006003 FC-2006001 FC-2006002 FC-2006003
----------- ----------- ----------- ----------- -------------------------------------------------------
2001 1000 1000 0 0 0 0 0
2002 1200 1200 0 0 0 0 0
2003 1300 1300 0 0 0 0 0
2004 1200 0 1200 0 0 0 0
2005 2600 0 2600 0 0 0 0
2006 9800 0 9800 0 0 0 0
2007 3540 0 3540 0 0 0 0
2008 1200 0 0 1200 0 0 0
2009 2200 0 0 2200 0 0 0
2010 3982 0 0 3982 0 0 0
2011 1200 0 0 1200 0 0 0
2001 1000 0 0 0 1000 0 0
2001 1200 0 0 0 1200 0 0
2003 1300 0 0 0 1300 0 0
2003 1200 0 0 0 0 1200 0
2005 2600 0 0 0 0 2600 0
2006 9800 0 0 0 0 9800 0
2007 3540 0 0 0 0 3540 0
2007 1200 0 0 0 0 0 1200
2009 2200 0 0 0 0 0 2200
2010 3982 0 0 0 0 0 3982
2010 1200 0 0 0 0 0 1200

Sorry about the layout. Thanks

This does not looks like inner join between Demo1 & Demo2 more like UNION


select ClientNo, Amount, 2006001, 2006002, 2006003, 0, 0, 0
from   tblDemo1
union all 
select ClientNo, Amount, 0, 0, 0, FC-2006001 FC-2006002 FC-2006003 
from   tblDemo2

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-07-20 : 10:19:23

quote:
Originally posted by sg2255551

Sorry about the layout

U or we don't need to be Sorry

if u make use of the code tags in this editor, as I am going to do here!!


ClientNo    Amount      2006001     2006002     2006003     FC-2006001     FC-2006002     FC-2006003 
----------- ----------- ----------- ----------- ------------------------------------------------------- 
2001        1000        1000        0           0		0		0		0
2002        1200        1200        0           0		0		0		0
2003        1300        1300        0           0		0		0		0
2004        1200        0           1200        0		0		0		0
2005        2600        0           2600        0		0		0		0
2006        9800        0           9800        0		0		0		0
2007        3540        0           3540        0		0		0		0
2008        1200        0           0           1200		0		0		0
2009        2200        0           0           2200		0		0		0
2010        3982        0           0           3982		0		0		0
2011        1200        0           0           1200		0		0		0
2001        1000        0	    0           0		1000        	0           	0
2001        1200        0	    0           0		1200        	0           	0
2003        1300        0	    0           0		1300        	0          	0
2003        1200        0	    0           0		0           	1200        	0
2005        2600        0	    0           0		0           	2600        	0
2006        9800        0	    0           0		0           	9800      	0
2007        3540        0	    0           0		0           	3540        	0
2007        1200        0	    0           0		0           	0           	1200
2009        2200        0	    0           0		0           	0           	2200
2010        3982        0	    0           0		0           	0           	3982
2010        1200        0	    0           0		0           	0           	1200

Srinika

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2006-07-21 : 10:00:09

Which html code tags did u used? I don't see when i go to view menu and view source?

I am a starting member and maybe i don't see many buttons on the html editor.Thanks

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-07-21 : 10:25:16

Hey dear,

When u use the editor of this forum, u should c the # button. U can select the text u want to be formatted and click that button.
Or else u can manually put code ..... /code tags [the tags should be sorrounded by square brackets]

Srinika

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-21 : 10:58:52

refer to the Forum FAQ http://www.sqlteam.com/forums/faq.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources