troubleshooting Question

Merge columns from multiple rows into one field problem

Avatar of mawdryn
mawdryn asked on
Microsoft SQL Server
6 Comments1 Solution312 ViewsLast Modified:
Hi,

I'm trying to combine data from one column on multiple rows into one field in MS SQL 2000.

ie,

From:
Accountno Reference
1234          ABC
1234          DEF
1234          GHI
2345          SDF
2345          AAA

into:
Accountno Reference
1234          ABC, DEF, GHI
2345          SDF, AAA

I have created a function to do this based on my google searches, however it will always return a null.
I'm not even sure if this is the best way to go about it, noting that there are thousands of such records.

Code for the function is as follows:

create function dbo.concatinstruments(@accountno varchar)
returns varchar(100)
as
begin
    declare @output varchar(100)
    select @output = coalesce(@output+', ','') + reference
    from instrumentview
    return @output
end

^ This doesn't work for some reason (I'm not a SQL expert)

however the following code (not a function) does work.,

declare @accountno varchar(100)
set @accountno = '1234'
declare @output varchar(100)
select @output = calesce(@output+', ','') + reference from instruments where accountno = @accountno
select accountno,@output from instruments where accountno = @accountno group by accountno

Am I calling the function wrong? I've never really dealt with functions before, so I'm dumb as a post on this.
Any help appreciated, Thanks.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 6 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros