How can i make a sum (concatenation) of strings of one column in a table.
for example i have a table like this
field1 field2
1 abc
1 bcd
2 sdf
2 sdd
I want to get these strings added group by field 1
Thanks
This cannot be done in a single query unless you employ some tricky hack. There is no obvious and reliable solution to do this in a single query. You'll have to use a cursor and a table variable for this purpose.
|||Hi,
with .Net 2.0 it is very easy. You can write "User defined aggregate" called for example StringConcat and use it in your queries like this
SELECT dbo.StringConcat(field2)FROM MyTableGROUP BY field1
and here is the code for your aggregate
C#
using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;using System.Text;using System.IO;using System.Runtime.InteropServices;[Serializable][SqlUserDefinedAggregate( Format.UserDefined,// user-defined serialization IsInvariantToDuplicates =false,// duplicates matter IsInvariantToNulls =true,// don't care about NULLs IsInvariantToOrder =false,// order matters (ignored) IsNullIfEmpty =false,// don't yield NULL if empty set MaxByteSize = 8000)]// maximum size in bytespublic struct StringConcat : IBinarySerialize{private StringBuilder sb;public void Init() {this.sb =new StringBuilder(); }public void Accumulate(SqlString s) {if (s.IsNull) {return;// skip NULLs }else {this.sb.Append(s.Value); } }public void Merge(StringConcat Group) {this.sb.Append(Group.sb); }public SqlString Terminate() {return new SqlString(this.sb.ToString()); }public void Read(BinaryReader r) { sb =new StringBuilder(r.ReadString()); }public void Write(BinaryWriter w) {if (this.sb.Length > 4000)// limit sb to 8000 bytes w.Write(this.sb.ToString().Substring(0, 4000));else w.Write(this.sb.ToString()); }}// end StringConcatVB
Imports SystemImports System.DataImports System.Data.SqlTypesImports Microsoft.SqlServer.ServerImports System.TextImports System.IOImports System.Runtime.InteropServices' user-defined serialization' duplicates matter' don't care about NULLs' order matters (ignored) ' don't yield NULL if empty set' maximum size in bytes is 8000<Serializable(), _ SqlUserDefinedAggregate( _ Format.UserDefined, _ IsInvariantToDuplicates:=False, _ IsInvariantToNulls:=True, _ IsInvariantToOrder:=False, _ IsNullIfEmpty:=False, _ MaxByteSize:=8000)> _Public Structure StringConcatImplements IBinarySerializePrivate sbAs StringBuilderPublic Sub Init()Me.sb =New StringBuilder()End Sub Public Sub Accumulate(ByVal sAs SqlString)If s.IsNullThen Return' skip NULLsElse Me.sb.Append(s.Value)End If End Sub Public Sub Merge(ByVal GroupAs StringConcat)Me.sb.Append(Group.sb)End Sub Public Function Terminate()As SqlStringReturn New SqlString(sb.ToString())End Function Public Sub Read(ByVal rAs BinaryReader) _Implements IBinarySerialize.Read sb =New StringBuilder(r.ReadString())End Sub Public Sub Write(ByVal wAs BinaryWriter) _Implements IBinarySerialize.WriteIf Me.sb.Length > 4000Then' limit sb to 8000 bytes w.Write(Me.sb.ToString().Substring(0, 4000))Else w.Write(Me.sb.ToString())End If End SubEnd Structure' end StringConcat
I hope this helps.
Let me know if this worked for you.
|||thanks, But I am looking for something within the sql
|||Hi,
After compiling the class into a DLL, you can import the DLL as a SQL Server assembly using either the Visual Studio 2005 Deploy option or manually using the CREATE ASSEMBLY statement and CREATE AGGREGATE statement as is shown in the following listing:
CREATE ASSEMBLY StringConcatFROM'C:\StringConcat.dll'GOCREATE AGGREGATE StringConcat(@.inputnvarchar)RETURNSnvarcharEXTERNALNAME StringConcat.StringConcatGO
Using CLR objects (functions, aggregates...) for string manipulations or complex calculations is way faster than using nested queries or Cursors.
I have used this aggregate in my projects and it works fine.
|||Hi, jiju-kj-
Of course it is possible to achieve this within the SQL. But you will need nested cursors the first cursor will iterate through distinct values in field1 and the inner cursor will iterate through all values from field2 for the current value in field1. I don't recommend you using cursors. But will decide which way is better for you. Let me know if you have problems with creating the cursors.
Cheers,
Paul
sql
vantages for performance,