Wednesday, December 13, 2006

SQL Server: How to concatenate field text across rows

Today I needed to concatenate text in a fields across rows. Specifically, given a table with columns for user accounts and quotas, report summing the user accounts, a list of the quota labels, and the summed quota amounts.

What you would want is something like this (But this won't work!):


-- WARNING: THIS WON'T WORK!
select user_accountID, concat(quotaNo), sum(quotaAmount)
from user_accounts
group by user_accountID

Alas, in SQL Server 2000, there isn't a built-in function to do this. About the only way I know of to do this is to custom build a user-defined function for the particular table you are working with. Here is my example:


-- Create a test table
create table user_accounts (user_accountID varchar(32), quotaNo varchar(32), quotaAmount float)

insert into user_accounts values('user_account1', 'quota1', 100)
insert into user_accounts values('user_account1', 'quota2', 200)
insert into user_accounts values('user_account1', 'quota3', 400)

insert into user_accounts values('user_account2', 'quota4', 800)
insert into user_accounts values('user_account2', 'quota5', 1600)

go

-- Create the user defined function
create function concat_quota_number(@user_account varchar(20))
returns varchar(200) as
begin
declare @str varchar(200)
select @str = coalesce(@str + ', ', '') + quotaNo from user_accounts where user_accountID = @user_account group by quotaNo
return @str
end

go

-- How to use it
select user_accountID, dbo.concat_quota_number(user_accountID) quota_labels, sum(quotaAmount) quota_sum
from user_accounts
group by user_accountID, dbo.concat_quota_number(user_accountID)


user_accountIDquota_labelsquota_sum
user_account1quota1, quota2, quota3700.0
user_account2quota4, quota52400.0

6 comments:

  1. Thanks Timothy, that's really useful!

    ReplyDelete
  2. Glad it worked for you. This is one of those annoyances in SQL Server that can only be solved with code (which is actually cool because it gives us a lot of job security!)

    ReplyDelete
  3. Hi Tim,

    I have a table like this

    ItemCode DimensionCode Dim_Value
    A100 D5001 10
    A100 D5002 15
    A100 D5003 17
    A101 D5001 15
    A101 D5002 10
    A101 D5003 12

    I need to Concatenate the DimValue for each ItemCode.

    like this

    ItemCode Item_Dim
    A100 W:10/H:15/L:17
    A101 W:15/H:10/L:12

    ---------------

    The DimesionCodes are
    D5001=W , D5002=H, D5003=L.

    DataTypes for the fields
    ItemCode is varchar(5)
    DimensionCode varchar(6)
    Dim_value numeric (10, 4)


    I follwed your example but I am getting an error cannot convert datatype varchar to numeric.


    How can I do it? Please Help.

    thanks in advance

    ReplyDelete
  4. How do you deal with concatenated strings that exceed 256 characters? I'm using MSSQL 2000 and all my concatenated strings are truncated to the 256-character limit.

    Thanks.

    ReplyDelete
  5. Urf, Sorry Roslen, I don't really know how you would handle that. I've changed jobs and no longer work with SQL Server. I would quess that you could probably write a couple of functions-- one for the first 256 characters, one for the second, etc, and then use those to extract the concatanated field in chunks. Not pretty, but neither is the original soultion...

    ReplyDelete
  6. It worked for me. Thanks Tim, I agree with the Job Security part :) Are you still working at FEMA?

    I gave you kudos here

    http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/d89e801ab681bdac#

    and also here

    http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/d72d4edfd7348d9c#

    ReplyDelete

I moderate comments blog posts over 14 days old. This keeps a lot of spam away. I generally am all right about moderating. Thanks for understanding.