Data Manipulation of Name field

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 of Name field

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2006-05-08 : 09:44:20

Guys,

I have following scenario

gid sname fname
________________________________________
1 'Mathew Levey' 'Mark Bell'
1 'San Gordon' 'Mark Bell'
1 'Larry Gomes' 'Mark Bell'
1 'Mathew Levey' 'Carol schnyder'

I want to split the above data into a table A

table A populated with rows with rows for each distinct occurence of sname and fname ignoring
any blanks '', Also sname, fname should be identified by field role populated with 1 for sname
and 2 for fname

gid Name Role
____________________________________
1 'Mathew Levey' 1
1 'San Gordon' 1
1 'Larry Gomes' 1
1 'Mark Bell' 2
1 'Carol Schnyder' 2

I tried doing this by stored procedure using gid and autogenerated id (1, 1) but doesnt seem to work.

Any ideas/suggesting how this can acheived

Thanks

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-05-08 : 11:17:46

Does this work for you?

SELECT DISTINCT gid, sname, 1
FROM tableA

UNION ALL

SELECT DISTINCT gid, fname, 2
FROM tableA

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2006-05-08 : 14:43:36

I think this statement would work, I have question though

will the data be inserted in the tablea in order that the names appear in the source table, I ask this because
while using union clause I cannot use order by clause.

Thanks

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-05-08 : 14:45:18

select * from (
SELECT DISTINCT gid, sname, 1
FROM tableA

UNION ALL

SELECT DISTINCT gid, fname, 2
FROM tableA
) Subquery
order by [Whatever]

...but why do you care what order the data is inserted (cautionary flags going up).

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-05-08 : 15:11:59

Why can't you do an ORDER BY? You actually don't need to wrap the UNION into a subquery:

SELECT DISTINCT gid, sname, 1
FROM tableA

UNION ALL

SELECT DISTINCT gid, fname, 2
FROM tableA

ORDER BY sname

And it's not going to matter what order you insert the records, because SQL Server doesn't store the records in any particular order, unless there is a clustered index on the table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources