mawdryn
asked on
Merge columns from multiple rows into one field problem
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(@acc
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.
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(@acc
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.
Last Comment
ASKER
Hi,
My apologies, the missing where clause in my function was a typo on my part.
That's how I was calling the function (sorry I didn't insert that code), however I am getting the null values outputted by the function.
The second snippet of code I placed in my first post was to test that the coalesce and variables were working as designed.
My apologies, the missing where clause in my function was a typo on my part.
That's how I was calling the function (sorry I didn't insert that code), however I am getting the null values outputted by the function.
The second snippet of code I placed in my first post was to test that the coalesce and variables were working as designed.
Try to use RTrim and Ltrim to truncate the blank
create function dbo.concatinstruments(@acc
returns varchar(100)
as
begin
declare @output varchar(100)
select @output = coalesce(@output+', ','') + RTrim(reference)
from instrumentview where RTrim(Accountno)=@accountn
return @output
end
select Accountno,dbo.concatinstru
group by Accountno
create function dbo.concatinstruments(@acc
returns varchar(100)
as
begin
declare @output varchar(100)
select @output = coalesce(@output+', ','') + RTrim(reference)
from instrumentview where RTrim(Accountno)=@accountn
return @output
end
select Accountno,dbo.concatinstru
group by Accountno
ASKER
Hi,
I've added return @accountno after the begin statement in the function, and found that it only appears to be passing the first character of the accountno to the function. For the life of me cannot figure out why..
I've added return @accountno after the begin statement in the function, and found that it only appears to be passing the first character of the accountno to the function. For the life of me cannot figure out why..
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Solved. changing the heading to
create function dbo.concatinstruments(@acc
brought through the complete accountno and thus no more nulls.
I'll give you the points HuyBD for helping me out.
create function dbo.concatinstruments(@acc
brought through the complete accountno and thus no more nulls.
I'll give you the points HuyBD for helping me out.
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
create function dbo.concatinstruments(@acc
returns varchar(100)
as
begin
declare @output varchar(100)
select @output = coalesce(@output+', ','') + reference
from instrumentview where Accountno=@accountno
return @output
end
And call it
select Accountno,dbo.concatinstru
group by Accountno