Concatenating field for multiple records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Concatenating field for multiple records

Author

Topic

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-05-30 : 14:16:28

Quick question. What I'm trying to do is concatenate a field for multiple records (hope that is worded in an understandable manner). Here's an example:

ID.....Code
5......33
5......23

ID.....Code...Result
5......33.....33 23
5......23.....33 23

I need the code to get the Result field. I know the code if you were to find the sum, min, max, etc...

(SELECT SUM(CODE)
FROM Table
WHERE ID = Table.ID) AS Result

But I don't know how to write it so it will combine strings.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-30 : 14:19:41

One method:-

SELECT t.ID,LEFT(cl.CodeList,LEN(cl.CodeList)-1) AS Result
FROM Table t
CROSS APPLY (SELECT CAST(Code AS varchar(4)) + ' ' AS [text()]
             FROM Table
             WHERE ID =t.ID
             FOR XML PATH(''))cl(CodeList)

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-05-30 : 14:41:38

This doesn't appear to work totally.

For a guy with a code 20 and 34, the result I get using your code is "20 3". The last digit gets cut off.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-30 : 14:44:23

quote:
Originally posted by im1dermike

This doesn't appear to work totally.

For a guy with a code 20 and 34, the result I get using your code is "20 3". The last digit gets cut off.

then mke it like this

SELECT t.ID,cl.CodeList AS Result
FROM Table t
CROSS APPLY (SELECT CAST(Code AS varchar(4)) + ' ' AS [text()]
             FROM Table
             WHERE ID =t.ID
             FOR XML PATH(''))cl(CodeList)

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-05-30 : 14:48:55

That works in a query, but I need it in a view and it doesn't like the dual SELECTs I think. I get this error:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-30 : 15:03:17

quote:
Originally posted by im1dermike

That works in a query, but I need it in a view and it doesn't like the dual SELECTs I think. I get this error:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Refer this link too

http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/rowset-string-concatenation-which-method-is-best.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources