Web Development

Ask something
Discussion Locked

SQL Server 2000 Grouping...?

0 helpful
4 replies

by parier 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Tables Alias
---------------------- --------------
Employees ee
Skills sk
Emp_skills ese

Employee fields (table for all employees, listing only relevance fields)
Emp_id (PK)
Last_name
First_name
Emp_dept

Skill Fields (table for all skills, listing ony relevant fields)
Skill_id (PK)
Skill_desc

Emp_Skill fields (table which assigned employees skills are maintained)
Skill_id
Emp_id

The employee table is your typical employee table. There are unique emp_id?s for all employees. This report will also include the first, last name, emp_code and the employees dept.

Skill table is used to take an employee and match all skills they are assigned (by matching records in emp_skill) table and displaying employee names and skill names.

The only skill field that needs to display is the skill description. This is a single character identifier fir each skill. Non-manager positions are lowercase, and manangement skills are uppercase. It is possible for employees to have a upper and lowercase letter of the same value (ie. Aa,Bb,Cc, etc).

For each record matched (where there is an employee and skill I nemp_skill field I want to display the following:

Cook Supervisor
Barker, Bob ADFklmn
Mouse, Mickey DFGHJjklm

Prep Cook
Frost, Jack fghjikl
Fudd, Elmer lmnop
Wacks, Candel qrst

But when I do the join (excluding depts/dept names) I am getting...

Barker, Bob A
Barker, Bob D
Barker, Bob F
Barker, Bob k
Barker, Bob l
Barker, Bob m
Barker, Bob n
Mouse, Mickey D
Mouse, Mickey F
Mouse, Mickey G
Mouse, Mickey H
Mouse, Mickey J
Mouse, Mickey j
Mouse, Mickey k
Mouse, Mickey l
Mouse, Mickey m

How can I show each employee once and each skill in a single line ? I am NOT talking about suppressing duplicates. ALso, this will eventually have to be a stored procedure, how do I do that also?

Need more info, msg me ? Ill respond asap.

4 replies

by parier

Did some research and I have...something....Im close, but this reference does not show how to include the skills AND employee name, and other fields if I wanted to display them...here is what I am getting...

All skill identifiers for all employees per employee...
a duplicate for each employee where they have a skill (so if a employee has 12 skills, they show 12 times.) This was corrected by removeing the skill as a seperate column. I still need to show dept, emp name, emp code....where do I put that in this:

DECLARE @EmployeeList varchar(100)

SELECT @EmployeeList = COALESCE(@EmployeeList + '', '') +
CAST(skill_description AS varchar(5))
FROM tbl_trans_employee_skills s1, tbl_mst_skills s2, tbl_mst_employee ee
where s1.skill_id = s2.skill_id
and s1.emp_id = ee.emp_id
and ee.status = 'A'
and s2.status = 'A'
--SELECT @EmployeeList

select distinct @EmployeeList as "SKILLS",
dd.dept_code,
dd.dept_name,
ee.last_name,
ee.first_name,
sk.skill_description
from tbl_trans_employee_skills es, tbl_mst_Skills sk, tbl_mst_employee ee, tbl_mst_department dd
where es.emp_id = ee.emp_id
and sk.skill_id = sk.skill_id
and ee.homedept_id = dd.dept_id
and sk.skill_description is not null
and sk.skill_description <> ''
and ee.status = 'A'
and sk.status = 'A'
order by 1,3,4,5;

by medievaldude

Some code that I hope helps:

CREATE TABLE #tmpTest
(
FName VARCHAR(20),
LName VARCHAR(30),
Skill VARCHAR(10)
)

CREATE TABLE #tmpFinal
(
FName VARCHAR(20),
LName VARCHAR(30),
AllSkills VARCHAR(200)
)

INSERT INTO #tmpTest VALUES ('Bob', 'Barker', 'A')
INSERT INTO #tmpTest VALUES ('Bob', 'Barker', 'B')
INSERT INTO #tmpTest VALUES ('Bob', 'Barker', 'C')
INSERT INTO #tmpTest VALUES ('Bob', 'Barker', 'D')
INSERT INTO #tmpTest VALUES ('Bob', 'Barker', 'E')
INSERT INTO #tmpTest VALUES ('Bill', 'Parker', 'Q')
INSERT INTO #tmpTest VALUES ('Bill', 'Parker', 'R')
INSERT INTO #tmpTest VALUES ('Bill', 'Parker', 'S')
INSERT INTO #tmpTest VALUES ('Bill', 'Parker', 'T')

DECLARE @strFName VARCHAR(20)
DECLARE @strLName VARCHAR(30)
DECLARE @strSkill VARCHAR(10)
DECLARE @curFName VARCHAR(20)
DECLARE @curLName VARCHAR(30)
DECLARE @strAllSkills VARCHAR(200)

DECLARE curSkillCursor CURSOR
FOR SELECT * FROM #tmpTest
ORDER BY LName, FName, Skill

OPEN curSkillCursor

FETCH curSkillCursor INTO @strFName, @strLName, @strSkill

SET @curFName = @strFName
SET @curLName = @strLName
SET @strAllSkills = ''

WHILE @@FETCH_STATUS = 0
BEGIN
IF @strFName <> @curFName OR @strLName <> @curLName
BEGIN
SET @strAllSkills = LEFT(@strAllSkills, LEN(@strAllSkills) - 1)
INSERT INTO #tmpFinal VALUES (@curFName, @curLName, @strAllSkills)
SET @curFName = @strFName
SET @curLName = @strLName
SET @strAllSkills = ''
END

SET @strAllSkills = @strAllSkills + @strSkill + ', '

FETCH curSkillCursor INTO @strFName, @strLName, @strSkill
END

SET @strAllSkills = LEFT(@strAllSkills, LEN(@strAllSkills) - 1)
INSERT INTO #tmpFinal VALUES (@curFName, @curLName, @strAllSkills)

CLOSE curSkillCursor
DEALLOCATE curSkillCursor

SELECT * FROM #tmpFinal

DROP TABLE #tmpTest
DROP TABLE #tmpFinal

by parier

With the second posting I added, what am I doing wrong to show other fields, (dept, employee name, etc).

Over all, I need
dept_code, dept_name
skill_description
last_name, first_name...

This discussion is closed to new replies.