?
Solved

Concatanate the multiple rows from a query into one row

Posted on 2005-04-08
22
Medium Priority
?
305 Views
Last Modified: 2010-05-18
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?
0
Comment
Question by:pauldonson
  • 14
  • 8
22 Comments
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 13735029
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
0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 13735079
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



0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 13735154
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
0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 13735169
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
===========================================
0
 

Author Comment

by:pauldonson
ID: 13735229
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
0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 13735266
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
0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 13735277
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
0
 

Author Comment

by:pauldonson
ID: 13735386
Still getting Nulls in the skills column!
0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 13735408
can you type ur query and function heere
0
 

Author Comment

by:pauldonson
ID: 13735417
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
0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 13735496
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
0
 

Author Comment

by:pauldonson
ID: 13735511
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
0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 13735517
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


0
 

Author Comment

by:pauldonson
ID: 13735531
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
0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 13735538
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'
0
 

Author Comment

by:pauldonson
ID: 13735550
Plate Service
Buffet Service
Kitchen Porter
0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 13735560
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
0
 

Author Comment

by:pauldonson
ID: 13735576
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?
0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 13735582
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')

0
 
LVL 14

Accepted Solution

by:
Thandava Vallepalli earned 2000 total points
ID: 13735596
try this


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


0
 

Author Comment

by:pauldonson
ID: 13735607
That's the one!!! Thanks for your help (again!!)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question