INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

T-SQL Hints and Tips

How to organize SUM() for character field in SQL Server SELECT query by TomasDill
Posted: 4 Sep 01

There is a little problem for query to get something like a sum() for a character field to concatenate character values from all records in the group. Here is described solution.

The approach below have an limitation to max 30 records in group for concatenation. Test it if it will work for more records in group or there should be another limitation on your server, becaus ethis number is depended on SQL Server capabilities and length of the character field being concatenated.

The script works using a query like:


select T.*,
    rtrim(T.CharField) + ' ' +
    rtrim(IsNull(T1.CharField,''))+' '+
    rtrim(IsNull(T2.CharField,''))+' '+
    rtrim(IsNull(T3.CharField,'')) as CharResult
  from MyTable T
    left join MyTable T1 on T.GroupField=T1.GroupField
       AND T1.OrderField=T.OrderField+1
    left join MyTable T2 on T.GroupField=T2.GroupField
       AND T2.OrderField=T1.OrderField+1
    left join MyTable T3 on T.GroupField=T3.GroupField
       AND T3.OrderField=T2.OrderField+1
  WHERE T.OrderField in
    (select min(TT.OrderField) from MyTable TT where
        TT.GroupField=T.GroupField)


CharField - field required to be concatenated, GroupField - field for grouping, OrderField - custom field that contains order number of record inside of the group.

As you see, required to use as many joins as you have records in the group. Such query require to be built on the fly because its number of joins is depended on how many records are concatenated. In addition, additional field is required in the table to maintain exact order number of each record inside of each group, the table will look like following:


ID   CharField  GroupField OrderField   ...Other fields...
1    'First'    'Item1'    1
2    'Second'   'Item1'    2
3    'First'    'Item2'    1
4    'Second'   'Item2'    2
5    'Third'    'Item2'    3
6    'Finish'   'Item2'    4


The sample query will produce a result:

ID   CharField  GroupField OrderField  CharResult
1    'First'    'Item1'    1           'First Second'
3    'First'    'Item2'    1           'First Second Third Finish'


Following is the script that builds such query based on number of records in groups.

You can run it as a single SQL call to SQL Server, for example, using SQLEXEC() function in VFP (if you use VFP views, you can get connection handle using CursorGetProp('ConnectHandle','SomeViewAliasName')).
You can also organize it as a stored procedure. To return data from it into another SP or T-SQL script, use INSERT command into a temporary table.


declare @maxnum int, @num int, @lcStr varchar(8000), @lcStr2 varchar(8000),
  @lcStr3 varchar(8000), @lcnum varchar(10)
-- calculate the max number of records/group
select @maxnum=max(cnt) from (select count(*) as cnt from MyTable group by GroupField) a

-- prepare SQl Statement in a string
select @lcStr = 'select T0.*, convert(text,rtrim(T0.CharField)'
select @lcStr2 = ') as CharResult from MyTable T0 '
select @lcStr3 = ' WHERE T0.OrderField in (select min(TT.OrderField) from MyTable TT where TT.GroupField=T0.GroupField)'

-- limitation. Correct this after testing on your SQL Server
if @maxnum>30
  select @maxnum=30

select @num=1
while @num<=@maxnum-1
begin
  select @lcnum = ltrim(rtrim(convert(varchar(10),@num)))
  select @lcStr = @lcStr + '+'' ''+rtrim(IsNull(T'+@lcnum+'.CharField,''''))'
  select @lcStr2 = @lcStr2 + ' left join MyTable T'+@lcnum+' on T0.GroupField=T'+@lcnum
     +'.GroupField AND T'+@lcnum+'.OrderField=T'
     + ltrim(rtrim(convert(varchar(10),@num-1)))
     +'.OrderField+1'
  select @num=@num+1
end

-- run a query from string
execute (@lcStr + @lcStr2 + @lcStr3)


Finally, to populate the OrderField correctly after you added it, you can use following updating command:


Update MyTable SET MyTable.OrderField =
  (select count(*) from MyTable T where T.[ID]<MyTable.[ID]
    AND T.GroupField=MyTable.GroupField)+1


Where "ID" is unique identity key field in the table. It will assure that records in group all has correct order number. This updating command use the Identity key to sort records. however, youc an use another sorting order, for example, to sort strings in the result concatenated string in alphabetical order:


Update MyTable SET MyTable.OrderField =
  (select count(*) from MyTable T where T.GroupField=MyTable.GroupField AND
      T.CharField + convert(char(12), T.[ID]) <
        MyTable.CharField + convert(char(12), MyTable.[ID])
   )+1


All above commands and scripts could make a good use of indexes by ID, GroupField and OrderField fields.


Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close