Query Question

[ryand09]

I've got a parent and child table. When I run the query it returns a line for each record in the Child table. What I need to do is to concatenate the child records to only return one line for each record in the parent table. I could write a function in ASP and pass the parent ID, but I want to do this in a stored procedure or SQL query, but have no idea where to start.

SELECT p.P_ID, p.P_Name, m.LName + ', ' + m.Fname EmpName, m.Role
FROM ProjectMgrs AS m RIGHT JOIN Projects AS p ON m.ProjID = p.P_ID
WHERE (p.ProjID=41)
and (m.Role='M');

Thanks in advance

[andi_g69]

Which SQL Server version are you running?

[ryand09]

Version 7.0

[skarasik]

The best way to do this is with a cursor. Don't be intimidated by them. They work well, and this is exactly the type of situation where they are needed.

Try this code in a query analyzer window. Let me know if it works for you.

-- step one: create a temp table to hold the results of your query

create table #Results (NAME varchar(50) null, P_ID int null , Members varchar(500) null)

-- step two: Populate this table with the name and ID of each manager. We'll leave the members field blank
-- at this point, and populate it with a concatenation of all of the projects in the next step.

insert #Results (NAME, P_ID)
select NAME, P_ID from PROJECT_MANAGERS

-- step three: Use cursors to read through all of the records and concatenate them

declare @P_ID int
declare @text varchar(50)

declare c cursor
for select P_ID, m.LName + ', ' + m.Fname
from Projects

open c

fetch from c into @P_ID, @text

while @@fetch_status = 0
begin

update #Results
set Members = isnull(Members, '') + ', ' + @text
where P_ID = @P_ID



continue
end

close c
deallocate c

-- You now should have a table with all the information that you need.
select * from #results

[mist]

Originally posted by ryand09
I've got a parent and child table. When I run the query it returns a line for each record in the Child table. What I need to do is to concatenate the child records to only return one line for each record in the parent table. I could write a function in ASP and pass the parent ID, but I want to do this in a stored procedure or SQL query, but have no idea where to start.

SELECT p.P_ID, p.P_Name, m.LName + ', ' + m.Fname EmpName, m.Role
FROM ProjectMgrs AS m RIGHT JOIN Projects AS p ON m.ProjID = p.P_ID
WHERE (p.ProjID=41)
and (m.Role='M');

Thanks in advance

Post the DDL for the two tables and an example of what output you want and I'll have a look at it. I can't tell what you want to see from your question.

[le_chaman]

Hi since u r using the project table and the project managers table.
And on the output you want the projectid, the project name and also the name of the manager.
So it is perfectly normal to get as result each line in the projects because one manager can have many projects.
so the results can be like this :

P001 Project1 Manager1
P002 Project2 Manager1
P003 Project3 Manager1
P004 Project4 Manager2

If you want the list of managers on projects do a group by Manager if you want to group per projects do a group by p.ProjID

Hope this helps

Didier

[k2kataria]

Need to know a way to concatenate a column of strings using a select statement. Cant use cursors cause I am trying to design a View.

Say for eg

column1

abc
def
ghi
jkl

the select statement should return a concatenation of all rows

abcdefghijkl

Any Idea??

Ketan

[skhanal]

If it is just one column you want to return then,

declare @concat varchar(8000)

set @concat = ''

select @concat = @concat+column1
from yourtable

the problem with this solution is that anything beyond 8000 characters are truncated.

[k2kataria]

You can define a function named

create function concat_table_colum
returns nvarchar(8000)
as
begin
Declare @x varchar(8000)
select @x = @x + column name from table_name
return @x
end

and use this function in your select statement as in

select concat_table_name

Let me know if it works for you!

Ketan