Link to home
Start Free TrialLog in
Avatar of pauldonson
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.Person_ID INNER JOIN
                      dbo.TBL_Skills ON dbo.TBL_People_skills.Skill_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?
Avatar of Thandava Vallepalli
Thandava Vallepalli
Flag of United States of America image

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(@DeptNo 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(DeptNo)
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



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
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
===========================================
Avatar of pauldonson
pauldonson

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
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.Person_ID
                        INNER JOIN dbo.TBL_Skills ON dbo.TBL_People_skills.Skill_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.Person_ID INNER JOIN
       dbo.TBL_Skills ON dbo.TBL_People_skills.Skill_ID = dbo.TBL_Skills.Skill_ID
WHERE dbo.TBL_People.Person_name = @PNAME

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.Person_ID
                        INNER JOIN dbo.TBL_Skills ON dbo.TBL_People_skills.Skill_ID = dbo.TBL_Skills.Skill_ID
       )  A
ORDER BY A.Person_name
Still getting Nulls in the skills column!
can you type ur query and function heere
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.Person_ID INNER JOIN
       dbo.TBL_Skills ON dbo.TBL_People_skills.Skill_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.Person_ID
                    INNER JOIN dbo.TBL_Skills ON dbo.TBL_People_skills.Skill_ID = dbo.TBL_Skills.Skill_ID
       )  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.Person_ID
                    INNER JOIN dbo.TBL_Skills ON dbo.TBL_People_skills.Skill_ID = dbo.TBL_Skills.Skill_ID
       )  A
ORDER BY A.Person_name


itsvtk
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
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.Person_ID
                    INNER JOIN dbo.TBL_Skills ON dbo.TBL_People_skills.Skill_ID = dbo.TBL_Skills.Skill_ID


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
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.Person_ID INNER JOIN
       dbo.TBL_Skills ON dbo.TBL_People_skills.Skill_ID = dbo.TBL_Skills.Skill_ID
WHERE dbo.TBL_People.Person_name = 'Abbas Syed'
Plate Service
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.Person_ID INNER JOIN
       dbo.TBL_Skills ON dbo.TBL_People_skills.Skill_ID = dbo.TBL_Skills.Skill_ID
WHERE dbo.TBL_People.Person_name = @PNAME

RETURN @SNAME
END
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.Person_ID
                    INNER JOIN dbo.TBL_Skills ON dbo.TBL_People_skills.Skill_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?
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.Person_ID INNER JOIN
       dbo.TBL_Skills ON dbo.TBL_People_skills.Skill_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')

ASKER CERTIFIED SOLUTION
Avatar of Thandava Vallepalli
Thandava Vallepalli
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
That's the one!!! Thanks for your help (again!!)