combining rows in single table

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

Transact-SQL (2000)

combining rows in single table

Author

Topic

waxdart23
Starting Member

33 Posts

Posted - 2005-06-08 : 15:03:09

I am converting an association table from one system to another that holds data for associating customer records to each other (e.g. Father and Son, Employer and Employee). In the source system association table has two row for each association and in my new system there is only one row required.

The data in the source system is as follows:

AssociationID DonorID     AssocID     AssType                                           
21            4433        4434        Friend
22            4434        4433        Friend
25            719         603         Father
26            603         719         Son
71            2225        1343        husband
72            1343        2225        wife
97            5570        5327        Trust
98            5327        5570        Trust

I cannot work out how to creat one row for each association, ideally the data above will be as follows:

DonorID      DonorIDType       AssocID     AssocIDType 
4433         Friend            4434        Friend
719          Father            603         Son
2225         Husband           1343        Wife
5570         Trust             5327        Trust

Any help would be greatly appreciated.

Thanks
P

shebert
Yak Posting Veteran

85 Posts

Posted - 2005-06-08 : 15:29:15

the only way I can see to associate two items is to list the two itemid in a single table together.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-06-08 : 15:29:37

i think you'll need to use max or min function...
other than that i don't see the logic in your converting.

if you want to create 2 tables which have many to many relationships you'll need an
intermediate table that hold PK's from both tables. you can link the relationships there.

Go with the flow & have fun! Else fight the flow

waxdart23
Starting Member

33 Posts

Posted - 2005-06-08 : 15:33:48

This is the intermediate table, it will link one customer record to another from a customer details table.

Thanks
P

shebert
Yak Posting Veteran

85 Posts

Posted - 2005-06-08 : 16:27:01

what are the attributes of the table (column names) again?

waxdart23
Starting Member

33 Posts

Posted - 2005-06-08 : 18:07:17

If I could create just one row for each of the original tables' two rows I could create the reciprocal association value using conversion table as Father alway associates with Son etc.

So:
Source Table New Table
DonorID................Customer_No
AssType................Type
AssocID................Association_No

Thanks
P

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources