Combining multiple rows into one column 
Author Message
 Combining multiple rows into one column

I need help with the problem of combining or concatinating data from
multiple rows into one column for asp web report;

Senario; ASP & SQL SERVER 2K - 3 tables: [staff], [teams],
[staff_teams].([staff_teams] is a many-to-many relationship containing
keys staff_id and team_id)

I need to create a staff list from these tables. My issue is that I
get a row returned for every team a staff is in. I need all of a
staff's team(s) to be in one column on the same row. UUuuhhg!

Example Current Format:

staff_id |   name   |   team   |  phone |  title
   1         joe        blue     555     Mgr
   1         joe        red      555     Mgr
   2         jane       blue     444     Tech
   3         jeri       blue     333     Tech
   3         jeri       green    333     Tech

Example desired format;

staff_id |   name   | team      | phone | title
   1         joe      blue,red    555     Mgr
   2         jane     blue        444     Tech
   3         jeri     blue,green  333     Tech

Below is the current SQL query, it also does a LEFT OUTER JOIN to get
the staff who are not a member of any team:

SELECT staff.staff_id, staff.first_name, staff.title,
FROM staff
LEFT OUTER JOIN staff_teams ON staff.staff_id = staff_teams.staff_id
LEFT OUTER JOIN teams ON staff_teams.team_id = teams.team_id
WHERE (staff.first_name LIKE 'form_var_here %') AND (staff.last_name
LIKE 'form var_here %')

Any thoughts or suggestions would be greatly appreciated.

Sun, 13 Mar 2005 02:40:28 GMT
 [ 1 post ] 

 Relevant Pages 

1. column in multiple rows to multiple columns in one row

2. column in multiple rows to multiple columns in one row

3. how do I combine 24 rows(3 columns) into one row (

4. Combine Multiple Rows Into One

5. SELECT multiple rows back as one row with many columns

6. Combining duplicate rows with certain columns not duplicate into one

7. combine two rows as one row

8. combining rows from a table in to one row

9. combining data into one table from multiple tables - please disregard a previous one, wrong email

10. Combining multiple rows into a single row

11. DTS import - syntax for combining date and time columns into one datetime column

12. Concatenating multiple rows on one column

Powered by phpBB® Forum Software