Data Manipulation - creating new tables

Question

Data Manipulation - creating new tables

SSCarpal Tunnel

Points: 4936

April 12, 2006 at 1:20 pm

Guys,I have a follow scenario which I am trying to solvegr#toname    fromname_________________________________1John Doe  John Smith1Steve Martin    John Smith1John LooJohn Smith1John DoeJohn Wane2Mark Levey2Mark Twain3                       Julie Karr3                       Julie Ann4Tim Roost4Tony DykesJane RobertsI 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 fromnamefor eggr#tonamefromname__________________________________1John Doe        John Smith2Mark Levey3                       Julie Karr4Tony DykesJane RobertsTable B I want to create a row for each unique toname and fromname for corresponding gr#for eggr#tonamefromname_________________________________1John Doe1Steve Martin1John Loo1                       John Smith1                       John Wane4Tim Roost4Tony Dykes4                       Jane RobertsAny suggestions/inputs on how to go about doing this would helpThanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

[KH] SSCommitted Points: 1908 More actions · Answer 1

can you re-post or format your question to a more readable form ?

am-244616 SSCarpal Tunnel Points: 4936 More actions · Answer 2

Sorry about that I reformatted and posted it

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 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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

What do you mean by first? First alphabetically? Is there another column in the table which specifies the order?

Assuming first alphabetically, this should give you a starting point.

SELECT Gr#, MIN(ToName) as FirstToName, MIN(FromName) AS FirstFromName

FROM OriginalTable

GROUP BY Gr#

As for the second, I'm not quite clear on what you want. If I've understood correctly this might work, though there's probably a better way

SELECT Distinct Gr#, ToName, NULL AS FromName

FROM OriginalTable

UNION ALL

SELECT Distinct Gr#, NULL AS ToName, FromName

FROM OriginalTable

ORDER BY Gr#

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Bert De Haes Hall of Fame Points: 3098 More actions · Answer 4

your demand is not very clear ( what is first? ), but this script could be a start :

set nocount on

go

use tempdb

go

if object_id('ori') is not null drop table ori

if object_id('TableA') is not null drop table TableA

if object_id('TableB') is not null drop table TableB

go

create table ori

(

gr int not null,

toname varchar(30) not null,

fromname varchar(30) not null

)

go

insert ori (gr,toname,fromname)values (1,'John Doe','John Smith')

insert ori (gr,toname,fromname)values (1,'Steve Martin','John Smith')

insert ori (gr,toname,fromname)values (1,'John Loo','John Smith')

insert ori (gr,toname,fromname)values (1,'John Doe','John Wane')

insert ori (gr,toname,fromname)values (2,'Mark Levey','')

insert ori (gr,toname,fromname)values (2,'Mark Twain','')

insert ori (gr,toname,fromname)values (3,'','Julie Karr')

insert ori (gr,toname,fromname)values (3,'','Julie Ann')

insert ori (gr,toname,fromname)values (4,'Tim Roost','')

insert ori (gr,toname,fromname)values (4,'Tony Dykes','Jane Roberts')

go

-- table A

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

declare @max-2 varchar(30)

select @max-2 = replicate('Z',30)

select gr,

toname=replace(min(case when toname = '' then @max-2 else toname end),@max,''),

fromname=replace(min(case when fromname = '' then @max-2 else fromname end),@max,'')

into TableA

from ori

group by gr

go

-- Table B

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

select DISTINCT

gr,

toname,

fromname=''

into TableB

from ori

where toname <> ''

union

select DISTINCT

gr,

toname='',

fromname

from ori where fromname <> ''

go

select * from TableA

select * from TableB

go

/* Result :

TableA

gr	toname	fromname
1	John Doe	John Smith
2	Mark Levey
3		Julie Ann
4	Tim Roost	Jane Roberts

TableB

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