concatenate varchar field from multiple rows into one row as a group

concatenate varchar field from multiple rows into one row as a group

Post by Stephane Via » Sun, 05 Jan 2003 02:03:15



how can i return this
id name
10001 NICHOLET, LIZ|viau, steph

10013 10013 HICKEY, GERRY|HICKEY, PAT

from
id    name
10001 NICHOLET, LIZ
10001 viau, steph
10013 HICKEY, GERRY
10013 HICKEY, PAT

using a query

SELECT  PROPERTY_ID, AGENT_NM + '|' AS AGENT_NM
FROM AGENTS A JOIN SELLING_AGENTS SA ON A.AGENT_ID=SA.AGENT_ID
GROUP BY PROPERTY_ID, AGENT_NM
ORDER BY PROPERTY_ID

this query returns each row
id name
10001 NICHOLET, LIZ|
10001 viau, steph
10013 HICKEY, GERRY|
10013 HICKEY, PAT|

 
 
 

concatenate varchar field from multiple rows into one row as a group

Post by Anith Se » Sun, 05 Jan 2003 02:13:08


Use a client programming language and its string concatenation
functions for such manipulations. You cannot do such processes
reliably and efficiently using T-SQL.

Here are some ideas for cross-tab/pivoting & concatenation.

1. Use a series of CASE expressions & concatenate them. Check
   this newsgroup and you can find tons of examples. Also refer:
   http://support.microsoft.com/default.aspx?scid=kb;EN-US;q175574

2. Use some 3rd party tool which can do such concatenations, you
   can find some here
        http://www.rac4sql.net
        http://www.ag-software.com/

3. Also refer to:
        http://www.sqlmag.com/Articles/Index.cfm?ArticleID=15608
        http://www.sqlteam.com/item.asp?ItemID=2955

--
- Anith
(Please respond only to newsgroups)

 
 
 

1. concatenate multiple rows into 1 row

I'm working with a resultset like this:

SalesID
123
456
789

That I would like to look like this:

SalesID
123;456;789

Any great suggestions out there on how to do this?  I can do it using a
cursor, but I don't think that's the best way to do it.

Thanks, Andre

2. Single-Byte to Double-Byte Conversion Tool?

3. Concatenate ROWS to one ROW

4. view on a synonym

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

6. EASY SQL question!

7. IT Job Opportunities in the Dallas-Fort Worth area

8. Concatenating multiple rows on one column

9. Concatenating multiple rows into one

10. select case return rows in one row (how to group by three column)

11. Grouping multiple rows into 1 row during a select into