Arghh!! Split Record Problem - SQL Server Forums

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)

Arghh!! Split Record Problem

Author

Topic

alvinrc
Starting Member

2 Posts

Posted - 2002-06-13 : 11:34:33

I have a single table looks like this:

Table Name: Employee
Columns: Employee Name, DeductionCode1, DeductionCode2, DeductionCode3, etc.....

An Employee could have his deductions spread across those deductions codes and deductioncode1 from one employee could be different from another. The problem is I need a query that would list down each employee and his deductions as separate records so it would look something like this

Fields: Name,DeductionCode1,DeductionCode2
John Doe IDQ9 F0GH
Jane Doe TR3G BR22

After SQL:

Fields: Name,DeductionCode
John Doe IDQ9
John Doe F0GH
Jane Doe TR3G
Jane Doe BR22

Any help would be appreciated. Thanks.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-13 : 11:38:00

You should consider normalizing your design . . .


select
    name,
    deductioncode1
from
    employee
where
    deductioncode1 is not null
union all
select
    name,
    deductioncode2
from
    employee
where
    deductioncode2 is not null

<O>

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-06-13 : 11:41:15

Try this:

CREATE TABLE #Employee(empName varchar(50), Code1 varchar(50), Code2 varchar(50))

INSERT INTO #Employee(empName,Code1, Code2) VALUES('John Doe', 'IDQ9', 'F0GH')
INSERT INTO #Employee(empName,Code1, Code2) VALUES('Jane Doe', 'TR3G', 'BR22')

SELECT empName, Code1 FROM #Employee
UNION
SELECT empName, Code2 FROM #Employee

DROP TABLE #Employee

That's the first time I've used UNION, so you might want to look it up in BOL to make sure this is the best way to do such.

Michael

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-06-13 : 11:42:25

Not sure if it's the best answer but union will work

select employee name, deductioncode1 from employee
union all
select employee name, deductioncode2 from employee

and so on. I'd consider looking at changing your table layout. Use 2 tables, employee and deductions with a 1-many relationship between them
create table employee(
employeeID int idenetity(1,1),
employee_name varchar (50)
)

create table deductions(
deductionid int identity(1,1),
employeeID int
deduction varchar(5)
)

Then to get your answers its a simple innerjoin on the 2 tables using employeeID.

Edit : aww man.. double sniped.

Hey rob.. whats that sniped symbol you use?

-----------------------
The best answer = just do as rob or page47 say.

Edited by - M.e. on 06/13/2002 11:43:50

alvinrc
Starting Member

2 Posts

Posted - 2002-06-14 : 01:17:22

Thanks Guys,

The union worked but we probably, for long term , considering M.E.'s suggestion. Thanks....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources