COALESCE by UserID
-
I have posted this before but I have forgoten what the solution was.
Trying to Concatenate values in 1 column by UserID. Below is my query. But how do I GROUP BY UserID?
Table looks like this.
UserID UserText
1 ABC
1 DEF
2 ABC
3 ABC
3 XYZ
DECLARE @Text NVARCHAR(1000)
SELECT @Text = COALESCE(@Text + ',', '') + UserText
FROM UserText
SELECT @Text
-
-- prepare test data
declare @table table (UserID tinyint, usertext char(3))
insert @table
select 1, 'ABC' union all
select 1, 'DEF' union all
select 2, 'ABC' union all
select 3, 'ABC' union all
select 3, 'XYZ'
-- do the work
declare @output table(userid tinyint, usertext nvarchar(1000))
DECLARE @Text NVARCHAR(1000),
@userid tinyint
insert @output (userid)
select distinct userid from @table
select @userid = min(userid),
@text = null
from @output
while @userid is not null
begin
select @text = isnull(@text + ', ', '') + z.usertext
from (
select top 100 percent usertext
from @table
where userid = @userid
order by usertext
) z
update @output
set usertext = @text
where userid = @userid
select @userid = min(userid),
@text = null
from @output
where userid > @userid
end
select * from @output
N 56°04'39.16"
E 12°55'05.25" -
Heres a solution that doesnt use a loop (cursor) and should therefore by more efficient.
------------DDL----------------
-- prepare test data
declare
@table table (id int identity, UserID tinyint, usertext char(3), allusertexts varchar(1000))
insert
@table (UserID , usertext )
select
1, 'ABC' union all
select
1, 'DEF' union all
select
2, 'ABC' union all
select
3, 'ABC' union all
select
3, 'XYZ'
------------------------------------
declare
@user-id varchar(20), @usertext varchar(20)
update
t
set
@usertext = case when @user-id =UserID then @usertext +','+usertext else usertext end , allusertexts = @usertext, @user-id = UserID
from
@table t
update
@table
set
allusertexts = x.allusertexts
from
@table t
join
(select max(allusertexts)allusertexts, UserID from @table group by UserID)x
on
x.UserID = t.UserID
select
* from @table
Obviously you should test this first.
-
Or...
--data
create table users (UserId tinyint, usertext varchar(50))
insert users
select 1, 'ABC'
union all select 1, 'DEF'
union all select 2, 'ABC'
union all select 3, 'ABC'
union all select 3, 'XYZ'
--function
go
create function dbo.fnAllUserText (@UserId tinyint) returns varchar(1000) as
begin
declare @AllUserText varchar(1000)
select @AllUserText = isnull(@AllUserText + ', ', '') + usertext from users where UserId = @userid
return @AllUserText
end
go
--calculation
select UserId, dbo.fnAllUserText(UserId) as AllUserText from users group by UserId
--tidy
drop table users
drop function dbo.fnAllUserText
/*results
UserId AllUserText
------ --------------
1 ABC, DEF
2 ABC
3 ABC, XYZ
*/
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
More efficient?
I doubt that, since you concatenate and accumulated update all rows in @table, even the duplicates. Imagine you have thousands of UserIDs where all of them have a small number of related text rows.
Also, your example builds on the fact that there is an identity in the original source table and a column for all usertexts to be concatenated.
And your output looks like this:
ID UserID UserText AllUserTexts
-- ------ -------- ------------
1 1 ABC NULL
2 1 DEF NULL
3 2 ABC NULL
4 3 ABC NULL
5 3 XYZ NULL
I don't think your suggestion of an solution works.
N 56°04'39.16"
E 12°55'05.25" -
umm have you actually run the code. NOt sure why your getting nulls.
Rather than assuming id and extra column i am would insert the records in a tables variable which would perform much the same function as you @output table.
If duplicates were supposed to be removed then an intial distinct in to the Tv would be fine.
Surely the scenario you suggest with small numbers of rows per user would have a even greater performance hit in a looping solution?
If you have some test data i would be interested to see a cursor perform faster than an update?
-
Yes, I tried your code. I am using SQL Server 2000 with SP4. I copied all your code and pasted it into QA and ran it. The output above is what I got, along with these error messages.
Server: Msg 8152, Level 16, State 9, Line 23
String or binary data would be truncated.
The statement has been terminated.
Warning: Null value is eliminated by an aggregate or other SET operation.
Changing @usertext varchar(8000) to @usertext varchar(8000) solved the two error messages above. Changing to @usertext varchar(7999) did not help. After that, I got following output
ID UserID UserText AllUserTexts
-- ------ -------- ------------
1 1 ABC ABC
2 1 DEF ABC
3 2 ABC ABC
4 3 ABC ABC
5 3 XYZ ABC
N 56°04'39.16"
E 12°55'05.25" -
Jules - I have similar problems getting the code working. It's not important, however, since I am familiar with that method and I understand the idea.
This article and subsequent comments mention all 3 of the methods mentioned here...
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx
My preference (until I see proof that something can be quicker) is for the function approach, where possible.
Trigger - have we solved your problem?
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
By the way, to fix Jules' code, we just need the datatype of usertext to be varchar rather than char in the table (or to add an rtrim to the setting of @usertext).
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply