A function like this would do it:
CREATE FUNCTION [dbo].[GetConcatString]
(
)
RETURNS VARCHAR(4000)
AS
BEGIN
DECLARE @r VARCHAR(4000)
SELECT @r = ISNULL(@r+', ', '') + a_lname
FROM yourTable c
RETURN @r
END
and your select would be:
select [dbo].GetConcatString()
from yourTable
HTH,
Dharmendar Kumar
Email/ Fax Communications: http://www.realmagnet.com
Web Development: http://www.daksatech.com
Forum: SQL
Date: 04/26/2007 02:04 PM
Author: daniel kessler
> I have a authors table containing
> publicationsa_id
> a_fname
> a_lname
>
> I can concatenate the text with a_lname || '," || a_fname into
> the_name. I have a query that returns 3 rows, but I would like to
> have each of the rows have the concatenated text from all the rows in
> a column. For example,
>
> "lastname1,firstcharacter1;lastname2,firstcharacter2;lastname3,
> firstcharacter3;"
>
> Is this doable? I'll keep looking to figure it out.
>
> thank you.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 &
Flex 2
Free Trial
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU
Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:2875
Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
