pauldonson
asked on
Concatanate the multiple rows from a query into one row
I wish to produce a query (or function/procedure) that combines the results from the Many side of a join into one row.
The tables are:
TBL_People joined to TBL_People_skills on Person__ID
TBL_People_skills joined to TBL_skills on Skill_ID
The following query:
SELECT TOP 100 PERCENT dbo.TBL_People.Person_name , dbo.TBL_Skills.Skill_name
FROM dbo.TBL_People INNER JOIN
dbo.TBL_People_skills ON dbo.TBL_People.Person_ID = dbo.TBL_People_skills.Pers on_ID INNER JOIN
dbo.TBL_Skills ON dbo.TBL_People_skills.Skil l_ID = dbo.TBL_Skills.Skill_ID
ORDER BY dbo.TBL_People.Person_name
returns the following results:
Abbas Syed Plate Service
Abbas Syed Buffet Service
Abbas Syed Kitchen Porter
Abdulali Mulla Bar Work
Abdulali Mulla Kitchen Porter
Adam Fraiwald Kiosk Worker
Adam Fraiwald Plate Service
Adam Fraiwald Bar Work
etc...
What I would like it to do is return the results as follows:
Abbas Syed Plate Service, Buffet Service, Kitchen Porter
Abdulali Mulla Bar Work, Kitchen Porter
Adam Fraiwald Kiosk Worker, Plate Service, Bar Work
To be honest, I don't know where to start!!!
Can anyone offer any advice please?
The tables are:
TBL_People joined to TBL_People_skills on Person__ID
TBL_People_skills joined to TBL_skills on Skill_ID
The following query:
SELECT TOP 100 PERCENT dbo.TBL_People.Person_name
FROM dbo.TBL_People INNER JOIN
dbo.TBL_People_skills ON dbo.TBL_People.Person_ID = dbo.TBL_People_skills.Pers
dbo.TBL_Skills ON dbo.TBL_People_skills.Skil
ORDER BY dbo.TBL_People.Person_name
returns the following results:
Abbas Syed Plate Service
Abbas Syed Buffet Service
Abbas Syed Kitchen Porter
Abdulali Mulla Bar Work
Abdulali Mulla Kitchen Porter
Adam Fraiwald Kiosk Worker
Adam Fraiwald Plate Service
Adam Fraiwald Bar Work
etc...
What I would like it to do is return the results as follows:
Abbas Syed Plate Service, Buffet Service, Kitchen Porter
Abdulali Mulla Bar Work, Kitchen Porter
Adam Fraiwald Kiosk Worker, Plate Service, Bar Work
To be honest, I don't know where to start!!!
Can anyone offer any advice please?
https://www.experts-exchange.com/questions/21336167/Select-Query-Help.html
Similar one.....
DeptNo Empno
--------- --------
10 101
10 102
10 103
20 104
20 105
30 106
DeptNo EmpNo
-------- -------------
10, 101,102,103
20 104,105
30 106
Crate a function as below
CREATE FUNCTION dbo.fxn_ConcatEmpNos(@Dept No INTEGER)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @EmpNos VARCHAR(8000)
SET @EmpNos = ''
SELECT @EmpNos = @EmpNos + EmpNo + ',' -- there was an extra space here I've now removed
FROM YourTable
WHERE DeptNo = @DeptNo
ORDER BY EmpNo
IF RIGHT(@EmpNos , 1) = ','
SET @EmpNos = LEFT(@EmpNos , LEN(@EmpNos ) - 1)
RETURN @EmpNos
END
GO
Then use this function like:
SELECT DISTINCT DeptNo, dbo.fxn_ConcatEmpNos(DeptN o)
FROM YourTable
ORDER BY DeptNo
itsvtk
DeptNo Empno
--------- --------
10 101
10 102
10 103
20 104
20 105
30 106
DeptNo EmpNo
-------- -------------
10, 101,102,103
20 104,105
30 106
Crate a function as below
CREATE FUNCTION dbo.fxn_ConcatEmpNos(@Dept
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @EmpNos VARCHAR(8000)
SET @EmpNos = ''
SELECT @EmpNos = @EmpNos + EmpNo + ',' -- there was an extra space here I've now removed
FROM YourTable
WHERE DeptNo = @DeptNo
ORDER BY EmpNo
IF RIGHT(@EmpNos , 1) = ','
SET @EmpNos = LEFT(@EmpNos , LEN(@EmpNos ) - 1)
RETURN @EmpNos
END
GO
Then use this function like:
SELECT DISTINCT DeptNo, dbo.fxn_ConcatEmpNos(DeptN
FROM YourTable
ORDER BY DeptNo
itsvtk
here is yours....
Function
====================
CREATE FUNCTION dbo.MYFUN(@PNAME VARCHAR)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @SNAME VARCHAR(8000)
SET @SNAME = ''
SELECT @SNAME = @SNAME + T2.Skill_name + ','
SELECT T1.Person_name, T2.Skill_name
FROM dbo.TBL_People T1, dbo.TBL_People_skills T2, dbo.TBL_Skills T3
WHERE T1.Person_ID = T2.Person_ID AND T2.Skill_ID = T3.Skill_ID AND T1.Person_name = @PNAME
IF RIGHT(@SNAME , 1) = ','
SET @SNAME = LEFT(@SNAME , LEN(@SNAME ) - 1)
RETURN @SNAME
END
GO
==========================
Here is ur query
SELECT A.PERSON_NAME, MYFUN(T1.PERSON_NAME)
FROM (SELECT DISTINCT T1.Person_name
FROM dbo.TBL_People T1, dbo.TBL_People_skills T2, dbo.TBL_Skills T3
WHERE T1.Person_ID = T2.Person_ID AND T2.Skill_ID = T3.Skill_ID) A
ORDER BY A.Person_name
Function
====================
CREATE FUNCTION dbo.MYFUN(@PNAME VARCHAR)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @SNAME VARCHAR(8000)
SET @SNAME = ''
SELECT @SNAME = @SNAME + T2.Skill_name + ','
SELECT T1.Person_name, T2.Skill_name
FROM dbo.TBL_People T1, dbo.TBL_People_skills T2, dbo.TBL_Skills T3
WHERE T1.Person_ID = T2.Person_ID AND T2.Skill_ID = T3.Skill_ID AND T1.Person_name = @PNAME
IF RIGHT(@SNAME , 1) = ','
SET @SNAME = LEFT(@SNAME , LEN(@SNAME ) - 1)
RETURN @SNAME
END
GO
==========================
Here is ur query
SELECT A.PERSON_NAME, MYFUN(T1.PERSON_NAME)
FROM (SELECT DISTINCT T1.Person_name
FROM dbo.TBL_People T1, dbo.TBL_People_skills T2, dbo.TBL_Skills T3
WHERE T1.Person_ID = T2.Person_ID AND T2.Skill_ID = T3.Skill_ID) A
ORDER BY A.Person_name
Oooops.... try this function...
CREATE FUNCTION dbo.MYFUN(@PNAME VARCHAR)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @SNAME VARCHAR(8000)
SELECT @SNAME = COALESCE(@SNAME + ',' + T2.SILL_NAME, T2.SILL_NAME)
FROM dbo.TBL_People T1, dbo.TBL_People_skills T2, dbo.TBL_Skills T3
WHERE T1.Person_ID = T2.Person_ID AND T2.Skill_ID = T3.Skill_ID AND T1.Person_name = @PNAME
RETURN @SNAME
END
GO
CREATE FUNCTION dbo.MYFUN(@PNAME VARCHAR)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @SNAME VARCHAR(8000)
SELECT @SNAME = COALESCE(@SNAME + ',' + T2.SILL_NAME, T2.SILL_NAME)
FROM dbo.TBL_People T1, dbo.TBL_People_skills T2, dbo.TBL_Skills T3
WHERE T1.Person_ID = T2.Person_ID AND T2.Skill_ID = T3.Skill_ID AND T1.Person_name = @PNAME
RETURN @SNAME
END
GO
and here is ur query
========================== ========== ======
SELECT A.PERSON_NAME, DBO.MYFUN(A.PERSON_NAME)
FROM
(
SELECT DISTINCT T1.Person_name
FROM dbo.TBL_People T1, dbo.TBL_People_skills T2, dbo.TBL_Skills T3
WHERE T1.Person_ID = T2.Person_ID AND T2.Skill_ID = T3.Skill_ID
) A
ORDER BY A.Person_name
========================== ========== =======
==========================
SELECT A.PERSON_NAME, DBO.MYFUN(A.PERSON_NAME)
FROM
(
SELECT DISTINCT T1.Person_name
FROM dbo.TBL_People T1, dbo.TBL_People_skills T2, dbo.TBL_Skills T3
WHERE T1.Person_ID = T2.Person_ID AND T2.Skill_ID = T3.Skill_ID
) A
ORDER BY A.Person_name
==========================
ASKER
Thanks for that, it doesn't quite work though, in the last function you provided:
The line:
SELECT @SNAME = COALESCE(@SNAME + ',' + T2.SILL_NAME, T2.SILL_NAME)
I presume should be T3.SKILL_NAME - is this correct? I have changed it to this and all I get is Null values in the skills field
The line:
SELECT @SNAME = COALESCE(@SNAME + ',' + T2.SILL_NAME, T2.SILL_NAME)
I presume should be T3.SKILL_NAME - is this correct? I have changed it to this and all I get is Null values in the skills field
then try this one
SELECT A.PERSON_NAME, DBO.MYFUN(A.PERSON_NAME)
FROM
(
SELECT DISTINCT T1.Person_name
FROM dbo.TBL_People INNER JOIN
dbo.TBL_People_skills ON dbo.TBL_People.Person_ID = dbo.TBL_People_skills.Pers on_ID
INNER JOIN dbo.TBL_Skills ON dbo.TBL_People_skills.Skil l_ID = dbo.TBL_Skills.Skill_ID
) A
ORDER BY A.Person_name
CREATE FUNCTION dbo.MYFUN(@PNAME VARCHAR)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @SNAME VARCHAR(8000)
SELECT @SNAME = COALESCE(@SNAME + ',' + dbo.TBL_Skills.SILL_NAME, dbo.TBL_Skills.SILL_NAME)
FROM dbo.TBL_People INNER JOIN
dbo.TBL_People_skills ON dbo.TBL_People.Person_ID = dbo.TBL_People_skills.Pers on_ID INNER JOIN
dbo.TBL_Skills ON dbo.TBL_People_skills.Skil l_ID = dbo.TBL_Skills.Skill_ID
WHERE dbo.TBL_People.Person_name = @PNAME
RETURN @SNAME
END
GO
SELECT A.PERSON_NAME, DBO.MYFUN(A.PERSON_NAME)
FROM
(
SELECT DISTINCT T1.Person_name
FROM dbo.TBL_People INNER JOIN
dbo.TBL_People_skills ON dbo.TBL_People.Person_ID = dbo.TBL_People_skills.Pers
INNER JOIN dbo.TBL_Skills ON dbo.TBL_People_skills.Skil
) A
ORDER BY A.Person_name
CREATE FUNCTION dbo.MYFUN(@PNAME VARCHAR)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @SNAME VARCHAR(8000)
SELECT @SNAME = COALESCE(@SNAME + ',' + dbo.TBL_Skills.SILL_NAME, dbo.TBL_Skills.SILL_NAME)
FROM dbo.TBL_People INNER JOIN
dbo.TBL_People_skills ON dbo.TBL_People.Person_ID = dbo.TBL_People_skills.Pers
dbo.TBL_Skills ON dbo.TBL_People_skills.Skil
WHERE dbo.TBL_People.Person_name
RETURN @SNAME
END
GO
Oooops....!
query change
SELECT A.PERSON_NAME, DBO.MYFUN(A.PERSON_NAME)
FROM
(
SELECT DISTINCT dbo.TBL_People.Person_name
FROM dbo.TBL_People INNER JOIN
dbo.TBL_People_skills ON dbo.TBL_People.Person_ID = dbo.TBL_People_skills.Pers on_ID
INNER JOIN dbo.TBL_Skills ON dbo.TBL_People_skills.Skil l_ID = dbo.TBL_Skills.Skill_ID
) A
ORDER BY A.Person_name
query change
SELECT A.PERSON_NAME, DBO.MYFUN(A.PERSON_NAME)
FROM
(
SELECT DISTINCT dbo.TBL_People.Person_name
FROM dbo.TBL_People INNER JOIN
dbo.TBL_People_skills ON dbo.TBL_People.Person_ID = dbo.TBL_People_skills.Pers
INNER JOIN dbo.TBL_Skills ON dbo.TBL_People_skills.Skil
) A
ORDER BY A.Person_name
ASKER
Still getting Nulls in the skills column!
can you type ur query and function heere
ASKER
Here goes:
CREATE FUNCTION dbo.fnSkills(@PNAME VARCHAR)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @SNAME VARCHAR(8000)
SELECT @SNAME = COALESCE(@SNAME + ',' + dbo.TBL_Skills.SKILL_NAME, dbo.TBL_Skills.SKILL_NAME)
FROM dbo.TBL_People INNER JOIN
dbo.TBL_People_skills ON dbo.TBL_People.Person_ID = dbo.TBL_People_skills.Pers on_ID INNER JOIN
dbo.TBL_Skills ON dbo.TBL_People_skills.Skil l_ID = dbo.TBL_Skills.Skill_ID
WHERE dbo.TBL_People.Person_name = @PNAME
RETURN @SNAME
END
Query:
SELECT A.PERSON_NAME, DBO.fnskills(A.PERSON_NAME )
FROM
(
SELECT DISTINCT dbo.TBL_People.Person_name
FROM dbo.TBL_People INNER JOIN
dbo.TBL_People_skills ON dbo.TBL_People.Person_ID = dbo.TBL_People_skills.Pers on_ID
INNER JOIN dbo.TBL_Skills ON dbo.TBL_People_skills.Skil l_ID = dbo.TBL_Skills.Skill_ID
) A
ORDER BY A.Person_name
CREATE FUNCTION dbo.fnSkills(@PNAME VARCHAR)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @SNAME VARCHAR(8000)
SELECT @SNAME = COALESCE(@SNAME + ',' + dbo.TBL_Skills.SKILL_NAME,
FROM dbo.TBL_People INNER JOIN
dbo.TBL_People_skills ON dbo.TBL_People.Person_ID = dbo.TBL_People_skills.Pers
dbo.TBL_Skills ON dbo.TBL_People_skills.Skil
WHERE dbo.TBL_People.Person_name
RETURN @SNAME
END
Query:
SELECT A.PERSON_NAME, DBO.fnskills(A.PERSON_NAME
FROM
(
SELECT DISTINCT dbo.TBL_People.Person_name
FROM dbo.TBL_People INNER JOIN
dbo.TBL_People_skills ON dbo.TBL_People.Person_ID = dbo.TBL_People_skills.Pers
INNER JOIN dbo.TBL_Skills ON dbo.TBL_People_skills.Skil
) A
ORDER BY A.Person_name
can you type the output of below query here
SELECT A.PERSON_NAME, DBO.fnskills(A.PERSON_NAME )
FROM
(
SELECT DISTINCT dbo.TBL_People.Person_name
FROM dbo.TBL_People INNER JOIN
dbo.TBL_People_skills ON dbo.TBL_People.Person_ID = dbo.TBL_People_skills.Pers on_ID
INNER JOIN dbo.TBL_Skills ON dbo.TBL_People_skills.Skil l_ID = dbo.TBL_Skills.Skill_ID
) A
ORDER BY A.Person_name
itsvtk
SELECT A.PERSON_NAME, DBO.fnskills(A.PERSON_NAME
FROM
(
SELECT DISTINCT dbo.TBL_People.Person_name
FROM dbo.TBL_People INNER JOIN
dbo.TBL_People_skills ON dbo.TBL_People.Person_ID = dbo.TBL_People_skills.Pers
INNER JOIN dbo.TBL_Skills ON dbo.TBL_People_skills.Skil
) A
ORDER BY A.Person_name
itsvtk
ASKER
Abbas Syed NULL
Abdulali Mulla NULL
Adam Fraiwald NULL
Adam Kaye NULL
Adam Norris NULL
Adele Done NULL
Aden Cochrane NULL
Adrian Barratt-Smith NULL
Aimee Bradshaw NULL
Akanimo Odon NULL
Abdulali Mulla NULL
Adam Fraiwald NULL
Adam Kaye NULL
Adam Norris NULL
Adele Done NULL
Aden Cochrane NULL
Adrian Barratt-Smith NULL
Aimee Bradshaw NULL
Akanimo Odon NULL
sorry not the above one plz give below on
SELECT DISTINCT dbo.TBL_People.Person_name , dbo.TBL_Skills.Skill_name
FROM dbo.TBL_People INNER JOIN
dbo.TBL_People_skills ON dbo.TBL_People.Person_ID = dbo.TBL_People_skills.Pers on_ID
INNER JOIN dbo.TBL_Skills ON dbo.TBL_People_skills.Skil l_ID = dbo.TBL_Skills.Skill_ID
SELECT DISTINCT dbo.TBL_People.Person_name
FROM dbo.TBL_People INNER JOIN
dbo.TBL_People_skills ON dbo.TBL_People.Person_ID = dbo.TBL_People_skills.Pers
INNER JOIN dbo.TBL_Skills ON dbo.TBL_People_skills.Skil
ASKER
Abbas Syed Buffet Service
Abbas Syed Kitchen Porter
Abbas Syed Plate Service
Abdulali Mulla Bar Work
Abdulali Mulla Kitchen Porter
Adam Fraiwald Bar Work
Adam Fraiwald Kiosk Worker
Adam Fraiwald Plate Service
Adam Kaye Buffet Service
Adam Kaye Chef 2
Adam Kaye Plate Service
Adam Norris Buffet Service
Adam Norris Catering Assistant
Adam Norris Kitchen Porter
Adam Norris Plate Service
Adele Done Bar Work
Adele Done Buffet Service
Adele Done Catering Assistant
Adele Done Silver Service
Aden Cochrane Buffet Service
Aden Cochrane Catering Assistant
Aden Cochrane Chef 1
Aden Cochrane Chef 2
Aden Cochrane Commis Chef
Aden Cochrane Kitchen Porter
Adrian Barratt-Smith Breakfast Chef
Adrian Barratt-Smith Chef 1
Adrian Barratt-Smith Chef Manager
Adrian Barratt-Smith Head Chef
Aimee Bradshaw Buffet Service
Aimee Bradshaw Catering Assistant
Aimee Bradshaw Food Service Work
Aimee Bradshaw Kiosk Worker
Aimee Bradshaw Kitchen Worker
Aimee Bradshaw Plate Service
Akanimo Odon Bar Work
Akanimo Odon Catering Assistant
Akanimo Odon Kitchen Porter
Akanimo Odon Plate Service
Akanimo Odon Silver Service
Abbas Syed Kitchen Porter
Abbas Syed Plate Service
Abdulali Mulla Bar Work
Abdulali Mulla Kitchen Porter
Adam Fraiwald Bar Work
Adam Fraiwald Kiosk Worker
Adam Fraiwald Plate Service
Adam Kaye Buffet Service
Adam Kaye Chef 2
Adam Kaye Plate Service
Adam Norris Buffet Service
Adam Norris Catering Assistant
Adam Norris Kitchen Porter
Adam Norris Plate Service
Adele Done Bar Work
Adele Done Buffet Service
Adele Done Catering Assistant
Adele Done Silver Service
Aden Cochrane Buffet Service
Aden Cochrane Catering Assistant
Aden Cochrane Chef 1
Aden Cochrane Chef 2
Aden Cochrane Commis Chef
Aden Cochrane Kitchen Porter
Adrian Barratt-Smith Breakfast Chef
Adrian Barratt-Smith Chef 1
Adrian Barratt-Smith Chef Manager
Adrian Barratt-Smith Head Chef
Aimee Bradshaw Buffet Service
Aimee Bradshaw Catering Assistant
Aimee Bradshaw Food Service Work
Aimee Bradshaw Kiosk Worker
Aimee Bradshaw Kitchen Worker
Aimee Bradshaw Plate Service
Akanimo Odon Bar Work
Akanimo Odon Catering Assistant
Akanimo Odon Kitchen Porter
Akanimo Odon Plate Service
Akanimo Odon Silver Service
and output of this one also
SELECT dbo.TBL_Skills.SKILL_NAME
FROM dbo.TBL_People INNER JOIN
dbo.TBL_People_skills ON dbo.TBL_People.Person_ID = dbo.TBL_People_skills.Pers on_ID INNER JOIN
dbo.TBL_Skills ON dbo.TBL_People_skills.Skil l_ID = dbo.TBL_Skills.Skill_ID
WHERE dbo.TBL_People.Person_name = 'Abbas Syed'
SELECT dbo.TBL_Skills.SKILL_NAME
FROM dbo.TBL_People INNER JOIN
dbo.TBL_People_skills ON dbo.TBL_People.Person_ID = dbo.TBL_People_skills.Pers
dbo.TBL_Skills ON dbo.TBL_People_skills.Skil
WHERE dbo.TBL_People.Person_name
ASKER
Plate Service
Buffet Service
Kitchen Porter
Buffet Service
Kitchen Porter
ok try this function
ALTER FUNCTION dbo.fnSkills(@PNAME VARCHAR(500))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @SNAME VARCHAR(8000)
SET @SNAME = ' '
SELECT @SNAME = @SNAME + ',' + dbo.TBL_Skills.SKILL_NAME
FROM dbo.TBL_People INNER JOIN
dbo.TBL_People_skills ON dbo.TBL_People.Person_ID = dbo.TBL_People_skills.Pers on_ID INNER JOIN
dbo.TBL_Skills ON dbo.TBL_People_skills.Skil l_ID = dbo.TBL_Skills.Skill_ID
WHERE dbo.TBL_People.Person_name = @PNAME
RETURN @SNAME
END
ALTER FUNCTION dbo.fnSkills(@PNAME VARCHAR(500))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @SNAME VARCHAR(8000)
SET @SNAME = ' '
SELECT @SNAME = @SNAME + ',' + dbo.TBL_Skills.SKILL_NAME
FROM dbo.TBL_People INNER JOIN
dbo.TBL_People_skills ON dbo.TBL_People.Person_ID = dbo.TBL_People_skills.Pers
dbo.TBL_Skills ON dbo.TBL_People_skills.Skil
WHERE dbo.TBL_People.Person_name
RETURN @SNAME
END
ASKER
SELECT A.PERSON_NAME, DBO.fnskills(A.PERSON_NAME )
FROM
(
SELECT DISTINCT dbo.TBL_People.Person_name
FROM dbo.TBL_People INNER JOIN
dbo.TBL_People_skills ON dbo.TBL_People.Person_ID = dbo.TBL_People_skills.Pers on_ID
INNER JOIN dbo.TBL_Skills ON dbo.TBL_People_skills.Skil l_ID = dbo.TBL_Skills.Skill_ID
) A
ORDER BY A.Person_name
Now produces:
Abbas Syed ,Plate Service,Buffet Service,Kitchen Porter
Abdulali Mulla ,Bar Work,Kitchen Porter
Adam Fraiwald ,Plate Service,Kiosk Worker,Bar Work
Adam Kaye ,Plate Service,Buffet Service,Chef 2
Adam Norris ,Catering Assistant,Plate Service,Buffet Service,Kitchen Porter
Adele Done ,Catering Assistant,Silver Service,Bar Work,Buffet Service
Aden Cochrane ,Catering Assistant,Buffet Service,Kitchen Porter,Chef 1,Chef 2,Commis Chef
Adrian Barratt-Smith ,Chef Manager,Head Chef,Breakfast Chef,Chef 1
Aimee Bradshaw ,Catering Assistant,Food Service Work,Kiosk Worker,Kitchen Worker,Plate Service,Buffet Service
Akanimo Odon ,Catering Assistant,Plate Service,Silver Service,Kitchen Porter,Bar Work
Aldo Errico ,Chef 1,Chef 2
Alex Ribeiro ,Buffet Service,Silver Service,Plate Service,Bar Work
Alex Tudge ,Bar Work,Catering Assistant,Buffet Service,Kitchen Porter,Plate Service,Silver Service
Alex Young ,Chef 1,Chef 2
Alexander Morley ,Chef 1,Chef 2
Alexander Thomson ,Chef 1
Any chance we can get rid of the first comma?
FROM
(
SELECT DISTINCT dbo.TBL_People.Person_name
FROM dbo.TBL_People INNER JOIN
dbo.TBL_People_skills ON dbo.TBL_People.Person_ID = dbo.TBL_People_skills.Pers
INNER JOIN dbo.TBL_Skills ON dbo.TBL_People_skills.Skil
) A
ORDER BY A.Person_name
Now produces:
Abbas Syed ,Plate Service,Buffet Service,Kitchen Porter
Abdulali Mulla ,Bar Work,Kitchen Porter
Adam Fraiwald ,Plate Service,Kiosk Worker,Bar Work
Adam Kaye ,Plate Service,Buffet Service,Chef 2
Adam Norris ,Catering Assistant,Plate Service,Buffet Service,Kitchen Porter
Adele Done ,Catering Assistant,Silver Service,Bar Work,Buffet Service
Aden Cochrane ,Catering Assistant,Buffet Service,Kitchen Porter,Chef 1,Chef 2,Commis Chef
Adrian Barratt-Smith ,Chef Manager,Head Chef,Breakfast Chef,Chef 1
Aimee Bradshaw ,Catering Assistant,Food Service Work,Kiosk Worker,Kitchen Worker,Plate Service,Buffet Service
Akanimo Odon ,Catering Assistant,Plate Service,Silver Service,Kitchen Porter,Bar Work
Aldo Errico ,Chef 1,Chef 2
Alex Ribeiro ,Buffet Service,Silver Service,Plate Service,Bar Work
Alex Tudge ,Bar Work,Catering Assistant,Buffet Service,Kitchen Porter,Plate Service,Silver Service
Alex Young ,Chef 1,Chef 2
Alexander Morley ,Chef 1,Chef 2
Alexander Thomson ,Chef 1
Any chance we can get rid of the first comma?
if above doesn't work try with below one
ALTER FUNCTION dbo.fnSkills(@PNAME VARCHAR(500))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @SNAME VARCHAR(8000)
SET @SNAME = ' '
SELECT @SNAME = @SNAME + dbo.TBL_Skills.SKILL_NAME + ','
FROM dbo.TBL_People INNER JOIN
dbo.TBL_People_skills ON dbo.TBL_People.Person_ID = dbo.TBL_People_skills.Pers on_ID INNER JOIN
dbo.TBL_Skills ON dbo.TBL_People_skills.Skil l_ID = dbo.TBL_Skills.Skill_ID
WHERE dbo.TBL_People.Person_name = @PNAME
IF RIGHT(@SNAME , 1) = ','
SET @SNAME = LEFT(@SNAME , LEN(@SNAME ) - 1)
RETURN @SNAME
END
after creating the above function plz give me the out of belwo query
select DBO.fnskills('Abbas Syed')
ALTER FUNCTION dbo.fnSkills(@PNAME VARCHAR(500))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @SNAME VARCHAR(8000)
SET @SNAME = ' '
SELECT @SNAME = @SNAME + dbo.TBL_Skills.SKILL_NAME + ','
FROM dbo.TBL_People INNER JOIN
dbo.TBL_People_skills ON dbo.TBL_People.Person_ID = dbo.TBL_People_skills.Pers
dbo.TBL_Skills ON dbo.TBL_People_skills.Skil
WHERE dbo.TBL_People.Person_name
IF RIGHT(@SNAME , 1) = ','
SET @SNAME = LEFT(@SNAME , LEN(@SNAME ) - 1)
RETURN @SNAME
END
after creating the above function plz give me the out of belwo query
select DBO.fnskills('Abbas Syed')
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That's the one!!! Thanks for your help (again!!)