Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to organize SUM() for character field in SQL Server SELECT query

T-SQL Hints and Tips

How to organize SUM() for character field in SQL Server SELECT query

by  TomasDill  Posted    (Edited  )
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:

Code: 
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:

Code: 
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:
Code: 
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.

Code: 
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:

Code: 
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:

Code: 
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.

Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top