Creating multiple tables from a main 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 2005 Forums

Transact-SQL (2005)

Creating multiple tables from a main table

Author

Topic

omega1983
Starting Member

40 Posts

Posted - 2009-12-01 : 10:36:48

I have a table called dbo.hobby. In this table, one ID can have many different hobbies. I want to create 4 different tables. Here is an example
HobbyKey ID Hobby table
218 111 fishing hobby1
219 111 boating hobby2
220 112 fishing hobby1

I want to structure the tables so that ID 111 would appear in both hobby1 and hobby2 tables due to the different HobbyKeys Here is a sketch of what I have
CREATE TABLE dbo.hobby1
hobbykey pk,numeric(13) not null,
hobbyID fk,char(10) not null,
hobbycomm varchar(4000), null
FROM dbo.hobby
where hobbytype = 'hobby'
insert into dbo.hobby1 (hobbykey,hobbyid,hobbycomm)
select hobbykey,hobbyid,hobbycomm
from dbo.hobby
where hobbytype = 'hobby'
Essentially if an ID has only one hobby they appear in hobby1 table. For each additional hobby, they must appear in a separate table

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-01 : 11:22:33

[code]select * from
(
select *,row_number () over(partition by ID order by HobbyKey) as rn
from hobby
) a
where a.rn = 1[/code]
will give you all rows with the first hobby (ordered by HobbyKey)

[code]select * from
(
select *,row_number () over(partition by ID order by HobbyKey) as rn
from hobby
) a
where a.rn > 1[/code]
will give you all additional hobbies for each ID.

EDIT:
But I must ask, why are you doing this?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources