Data Manipulation - Create new tables

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)

Data Manipulation - Create new tables

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2006-04-12 : 14:16:55

Guys,

I have a follow scenario which I am trying to solve

gr# toname fromname
_________________________________
1 John Doe John Smith
1 Steve Martin John Smith
1 John Loo John Smith
1 John Doe John Wane
2 Mark Levey
2 Mark Twain
3 Julie Karr
3 Julie Ann
4 Tim Roost
4 Tony Dykes Jane Roberts

I want to create 2 tables from the above data

Table A to be able to create 1 row for each distinct gr# with first occurence of toname and fromname
for eg

gr# toname fromname
__________________________________
1 John Doe John Smith
2 Mark Levey
3 Julie Karr
4 Tony Dykes Jane Roberts

Table B I want to create a row for each unique toname and fromname for corresponding gr#
for eg

gr# toname fromname
_________________________________
1 John Doe
1 Steve Martin
1 John Loo
1 John Smith
1 John Wane
4 Tim Roost
4 Tony Dykes
4 Jane Roberts

Any suggestions/inputs on how to go about doing this would help

Thanks

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2006-04-12 : 16:40:02

I have reformatted the data interms of ids which will easily be readable

Guys,

I have a follow scenario which I am trying to solve

gr# tonameid fromnameid
_________________________________
1 12 22
1 13 22
1 14 22
1 12 23
2 16 00
2 17 00
3 00 24
3 00 25
4 18 00
4 19 26

I want to create 2 tables from the above data

Table A to be able to create 1 row for each distinct gr# with first occurence of toname and fromname
for eg

gr# tonameid fromnameid
__________________________________
1 12 22
2 16 00
3 00 24
4 18 26

Table B I want to create a row for each unique toname and fromname for corresponding gr#
for eg

gr# tonameid fromnameid
_________________________________
1 12 00
1 13 00
1 14 00
1 00 22
1 00 23
4 18 00
4 19 00
4 00 26

Any suggestions/inputs on how to go about doing this would help

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-12 : 18:03:31

Try the following 2 query


select	*
from
(
	select 	gr, tonameid, min(fromnameid) as fromnameid
	from	#temp
	group by gr, tonameid
) a
where	tonameid = (select top 1 tonameid from #temp x
			where	x.gr	= a.gr)
order by gr, tonameid, fromnameid

select	*
from	
(
	select	gr, tonameid, 0 as fromnameid
	from	#temp
	group by gr, tonameid
	union all
	select  gr, 0, fromnameid
	from	#temp
	group by gr, fromnameid
) u
order by gr, tonameid, fromnameid

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2006-04-12 : 22:07:11

Khtan thanks for the reply but I am trying to write query when toname and fromname are populated with names

any ideas/suggestions would help

gr# toname fromname
_________________________________
1 John Doe John Smith
1 Steve Martin John Smith
1 John Loo John Smith
1 John Doe John Wane
4 Tim Roost
4 Tony Dykes Jane Roberts

I want to create 2 tables from the above data

Table A to be able to create 1 row for each distinct gr# with first occurence of toname and fromname
for eg

gr# toname fromname
__________________________________
1 John Doe John Smith
4 Tim Roost Jane Roberts

Table B I want to create a row for each unique toname and fromname for corresponding gr#
for eg

gr# toname fromname
_________________________________
1 John Doe null
1 Steve Martin null
1 John Loo null
1 null John Smith
1 null John Wane
4 Tim Roost null
4 Tony Dykes null
4 null Jane Roberts

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-12 : 22:17:48

my query is based on your 2nd post. Any problem you encounter with it ?

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2006-04-13 : 08:23:30

khtan, your query works just fine for the 2nd post, but I am looking for the solution for 3rd post, any suggestions/inputs would help

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-13 : 09:32:14

the query i posted will also works for your 3rd post. Did you try it ?