在SQL Server2005中按列连接字符串的三种方法

[size=14pt][size=12pt][b]本文为原创，如需转载，请注明作者和出处，谢谢！
[/b][/size][/size][font=宋体]最近做一个项目，遇到一个在分组的情况下，将某一列的字段值（[/font]varchar[font=宋体]类型）连接起来的问题，类似于[/font]sum[font=宋体]函数对[/font]int[font=宋体]型字段值求和。[/font] [font=宋体]如有一个表[/font]t_table[font=宋体]，结构和数据如图[/font]1

[img=451,225]http://www.cnblogs.com/images/cnblogs_com/nokiaguy/clragg/01.jpg[/img]

                                          图1

[font=宋体][size=10.5pt]    其中要按着[/size][/font][font=&quot][size=10.5pt]xh[/size][/font][font=宋体][size=10.5pt]字段分组，并且将每一组[/size][/font][font=&quot][size=10.5pt]name[/size][/font][font=宋体][size=10.5pt]字段值连接起来。最终结果希望如图[/size][/font][font=&quot][size=10.5pt]2[/size][/font][font=宋体][size=10.5pt]所示


[img=217,87]http://www.cnblogs.com/images/cnblogs_com/nokiaguy/clragg/02.jpg[/img]

             图2
[/size][/font]
[font=宋体]表中的[/font]th[font=宋体]字段值对于每一个[/font]xh[font=宋体]值是唯一的，也是有限的，也就是说，对于一个[/font]xh[font=宋体]值，[/font]th[font=宋体]的值不会太多，如最多是[/font]10[font=宋体]个（从[/font]1[font=宋体]至[/font]10[font=宋体]）。[/font]
[font=宋体]以上需求最终想了三种方法来解决这个问题。[/font]
[color=Black][b][font=宋体]一、修改表结构[/font][/b][/color]
[font=宋体]如果是新的项目，可以考虑修改一下表的结构。如果[/font]t_table[font=宋体]的结构修改如下：[/font]

    xh     value1 value2
value3    value4     .... ....                 value10
   0001 123456 654321 456789  
   0002 12abcd 4d2r343 343dfd
   0003 abcde3 132323
[font=宋体]这种方法将[/font]value[font=宋体]的值纵向改为横向，也就是说，按每一个[/font]xh[font=宋体]值，将[/font]value[font=宋体]字段的值按逆时针旋转了[/font]90[font=宋体]度。[/font] [font=宋体]但这种方法要有一个前提，就是假设[/font]xh[font=宋体]的每一个值所对应的[/font]value[font=宋体]值不会太多，如上面不超过[/font]10[font=宋体]个，这样才有可能建立有限个字段。如果按着上面的字段结构，只需要将这些字段加一起就可以了，也不用分组。如下所示：[/font]

[color=Black]select xh , (value1 + value2 + value3 + [img]http://www.cnblogs.com/Images/dot.gif[/img] + value10) as value from t_table[/color]
[font=宋体]但这种方法至少有如下三个缺陷：[/font]
1. [font=宋体]需要修改表结构，这对于已经进行很长时间或是已经上线的项目产不适用[/font]
2. [font=宋体]对每一个[/font]xh[font=宋体]字段的[/font]value[font=宋体]取值数有限制，如果太多，就得建立很多字段。这样性能会降低。[/font]
3. [font=宋体]这样做虽然查询容易，但如果需要对每一个[/font]xh[font=宋体]的不同值频繁修改或加入新的值时，如果把它们都放到一行，容易因为行锁而降低性能。[/font]
[b][font=宋体][size=10.5pt]二、动态生成[/size][/font][font=&quot][size=10.5pt]select[/size][/font][font=宋体][size=10.5pt]语句[/size][/font]
[/b]
[font=宋体][size=10.5pt]让我们先看三条[/size][/font][font=&quot][size=10.5pt]SQL[/size][/font][font=宋体][size=10.5pt]语句：[/size][/font]
[color=Black]select xh,value as th1 from t_table where th=[b]1[/b]
select xh,value as th2 from t_table where th=[b]2[/b]
select xh,value as th3 from t_table where th=[b]3[/b]
[/color]

[font=宋体][size=10.5pt]这三条语句分别使用[/size][/font][font=&quot][size=10.5pt]th[/size][/font][font=宋体][size=10.5pt]字段按着所有[/size][/font][font=&quot][size=10.5pt]th[/size][/font][font=宋体][size=10.5pt]可能的值来查询[/size][/font][font=&quot][size=10.5pt]t_table[/size][/font][font=宋体][size=10.5pt]，这三条[/size][/font][font=&quot][size=10.5pt]SQL[/size][/font][font=宋体][size=10.5pt]语句所查询出来的记录如图[/size][/font][font=&quot][size=10.5pt]3[/size][/font][font=宋体][size=10.5pt]所示。[/size][/font][font=&quot][size=10.5pt]

[img=260,358]http://www.cnblogs.com/images/cnblogs_com/nokiaguy/clragg/03.jpg[/img]

                        图 3

[/size][/font]    [font=宋体]然后再使用下面的语句按着[/font]xh[font=宋体]分组：[/font]

[color=Black]    select xh from t_table group by xh[/color]

[font=宋体][size=10.5pt]得到的结果如图[/size][/font][font=&quot][size=10.5pt]4[/size][/font][font=宋体][size=10.5pt]所示。

[img=101,88]http://www.cnblogs.com/images/cnblogs_com/nokiaguy/clragg/04.jpg[/img]

       图4


[/size][/font][font=宋体]然后使用[/font]left join[font=宋体]，以图[/font]4[font=宋体]所示的表为最左边的表，进行连接，[/font]SQL[font=宋体]语句如下：[/font]

[color=Black]select a.xh, b.th1, c.th2, d.th3 from
(select xh from t_table group by xh) a
left join
(select xh,value as th1 from t_table where th=[b]1[/b]) b on a.xh=b.xh
left join
(select xh,value as th2 from t_table where th=[b]2[/b]) c on a.xh=c.xh
left join
(select xh,value as th3 from t_table where th=[b]3[/b]) d on a.xh=d.xh
[/color]


[font=宋体][size=10.5pt]之所以使用[/size][/font][font=&quot][size=10.5pt]left join[/size][/font][font=宋体][size=10.5pt]，是因为按着[/size][/font][font=&quot][size=10.5pt]th[/size][/font][font=宋体][size=10.5pt]查询后，有的表的某些[/size][/font][font=&quot][size=10.5pt]xh[/size][/font][font=宋体][size=10.5pt]值可以没有，如图[/size][/font][font=&quot][size=10.5pt]3[/size][/font][font=宋体][size=10.5pt]中的第三个表，就没有[/size][/font][font=&quot][size=10.5pt]0003[/size][/font][font=宋体][size=10.5pt]。如果使用内连接，[/size][/font][font=&quot][size=10.5pt]0003[/size][/font][font=宋体][size=10.5pt]就无法在记录集中体现。这面的[/size][/font][font=&quot][size=10.5pt]SQL[/size][/font][font=宋体][size=10.5pt]的查询结果如图[/size][/font][font=&quot][size=10.5pt]5[/size][/font][font=宋体][size=10.5pt]所示。


[img=240,85]http://www.cnblogs.com/images/cnblogs_com/nokiaguy/clragg/05.jpg[/img]

              图5

[/size][/font]
[font=宋体]然后我们就可以使用如下的语句来连接[/font][color=Black]th1[/color][font=宋体]、[/font][color=Black]th2[/color][font=宋体]和[/font][color=Black]th3[/color][font=宋体]了。[/font]


[color=Black]select xh, (th1+th2+th3) as th from myview[/color]
myview[font=宋体]表示将上面用[/font]left join[font=宋体]的语句保存成的视图。[/font]
[font=宋体][size=10.5pt]下面可以将这个过程写成一条[/size][/font][font=&quot][size=10.5pt]SQL[/size][/font][font=宋体][size=10.5pt]语句：[/size][/font]
[color=Black]select xh, (th1+th2+th3) as th from
(
select a.xh,  (case when b.th1 is null then '' else b.th1 end) as th1,
(case when c.th2 is null then '' else c.th2 end) as th2,
(case when d.th3 is null then '' else d.th3 end) as th3
from
(select xh from t_table group by xh) a  
left join
(select xh,value as th1 from t_table where th=[b]1[/b]) b on a.xh=b.xh
left join
(select xh,value as th2 from t_table where th=[b]2[/b]) c on a.xh=c.xh
left join
(select xh,value as th3 from t_table where th=[b]3[/b]) d on a.xh=d.xh
) x
[/color]

[color=Black]    [font=宋体]由于[/font]null[font=宋体]加上任何字符串都为[/font]null[font=宋体]，因此，使用[/font]case[font=宋体]语句来将[/font]null[font=宋体]转换为空串。上面的[/font]SQL[font=宋体]就会得到图[/font]2[font=宋体]所示的查询结果。也许有的读者会问，如果[/font]th[font=宋体]的可能取值可变呢！如[/font]xh[font=宋体]为[/font]0001[font=宋体]的[/font]th[font=宋体]值四个：[/font]1[font=宋体]至[/font]4[font=宋体]。[/font][font=宋体]那上面的[/font]SQL[font=宋体]不是要再加一个[/font]left join[font=宋体]吗？这样不是很不通用。[/font] [font=宋体]要解决这个问题也很容易。可以使用程序（如[/font]C#[font=宋体]、[/font]Java[font=宋体]等）自动生成上述的[/font]SQL[font=宋体]，然后由程序提交给数据库，再执行。[/font] [font=宋体]当然，这需要程序事先知道[/font]th[font=宋体]值对于当前程序最多有几个值，然后才可以自动生成上述的[/font]SQL[font=宋体]语句。[/font][/color]
[font=宋体][size=10.5pt]这种方法几乎适合于所有的数据库，不过如果[/size][/font][font=&quot][size=10.5pt]th[/size][/font][font=宋体][size=10.5pt]的取值比较多的话，可能[/size][/font][font=&quot][size=10.5pt]SQL[/size][/font][font=宋体][size=10.5pt]语句会很长，但是如果用程序自动生成的话，就不会管这些了。[/size][/font][b]
[font=宋体][size=10.5pt]三、使用[/size][/font][font=&quot][size=10.5pt]C#[/size][/font][font=宋体][size=10.5pt]实现[/size][/font][font=&quot][size=10.5pt]SQL Server2005[/size][/font][font=宋体][size=10.5pt]的扩展聚合函数（当然，也可以用[/size][/font][font=&quot][size=10.5pt]VB.NET[/size][/font][font=宋体][size=10.5pt]）[/size][/font]
[/b]
[font=宋体][size=10.5pt]这一种方法笔者认为是最“酷”的方法。因为每一个人都只想写如下的[/size][/font][font=&quot][size=10.5pt]SQL[/size][/font][font=宋体][size=10.5pt]语句就可以达到目录。[/size][/font]
[color=Black]select xh, dbo.joinstr(value) from t_table group by xh[/color]

[font=宋体]其中[/font]joinstr[font=宋体]是一个聚合函数，功能是将每一组的某个字符串列的值首尾连接。上面的[/font]SQL[font=宋体]也可以查询图[/font]2[font=宋体]所示的结果。但遗憾的是，[/font]sql server2005[font=宋体]并未提供可以连接字符串的聚合函数。下面我们就来使用[/font]C#[font=宋体]来实现一个扩展聚合函数。[/font]

[font=宋体][size=10.5pt]首先用[/size][/font][font=&quot][size=10.5pt]VS2008/VS2005[/size][/font][font=宋体][size=10.5pt]建立一个[/size][/font][font=&quot][size=10.5pt]SQL Server[/size][/font][font=宋体][size=10.5pt]项目，如图[/size][/font][font=&quot][size=10.5pt]6[/size][/font][font=宋体][size=10.5pt]所示。[/size][/font]
[img]http://www.cnblogs.com/images/cnblogs_com/nokiaguy/06.jpg[/img]

[color=Black]                                                                    图6[/color]


[font=宋体][size=10.5pt]点击“确定”按钮后，[/size][/font][font=&quot][size=10.5pt]SQL Server[/size][/font][font=宋体][size=10.5pt]项目会要求连接一个数据库，我们可以选择一个数据库，如图[/size][/font][font=&quot][size=10.5pt]7[/size][/font][font=宋体][size=10.5pt]所示。[/size][/font][font=&quot][size=10.5pt]

[img=305,302]http://www.cnblogs.com/images/cnblogs_com/nokiaguy/clragg/07.jpg[/img]

                                       图7

    [/size][/font][font=宋体][size=10.5pt]然后在工程中加入一个聚合类（[/size][/font][font=&quot][size=10.5pt]joinstr.cs[/size][/font][font=宋体][size=10.5pt]），如图[/size][/font][font=&quot][size=10.5pt]8[/size][/font][font=宋体][size=10.5pt]所示。[/size][/font][font=&quot][size=10.5pt]

[img]http://www.cnblogs.com/images/cnblogs_com/nokiaguy/clragg/08.jpg[/img]

                                                                                      图8
    joinstr.cs[/size][/font][font=宋体][size=10.5pt]中的最终代码如下：


[/size][/font][color=Black]using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.IO;
using System.Text;

[Serializable]
[SqlUserDefinedAggregate(
    Format.UserDefined, //use custom serialization to serialize the intermediate result
    IsInvariantToNulls = true, //optimizer property
    IsInvariantToDuplicates = false, //optimizer property
    IsInvariantToOrder = false, //optimizer property   
    MaxByteSize = 8000) //maximum size in bytes of persisted value
]

public struct joinstr :IBinarySerialize
{
    private System.Text.StringBuilder intermediateResult;
   
    public void Init()
    {
        // 在此处放置代码
        intermediateResult = new System.Text.StringBuilder();
    }

    public void Accumulate(SqlString Value)
    {
        intermediateResult.Append(Value.Value);
    }

    public void Merge(joinstr Group)
    {
        intermediateResult.Append(Group.intermediateResult);
    }

    public SqlString Terminate()
    {
        return new SqlString(intermediateResult.ToString());
    }

    public void Read(BinaryReader r)
    {
        intermediateResult = new StringBuilder(r.ReadString());
    }

    public void Write(BinaryWriter w)
    {
        w.Write(this.intermediateResult.ToString());
    }
}
[/color]

[font=宋体]由于本例需要聚合字符串，而不是已经被序列化的类型，如[/font]int[font=宋体]等，因此，需要实现[/font]IBinarySerialize[font=宋体]接口来手动序列化。使用[/font]C#[font=宋体]实现[/font]SQL Server[font=宋体]聚合函数，也会受到字符串最大长度为[/font]8000[font=宋体]的限制。[/font]

[font=宋体][size=10.5pt]在编写完上述代码后，可以使用[/size][/font][font=&quot][size=10.5pt]Visual Studio[/size][/font][font=宋体][size=10.5pt]来部署（右向工程，在弹出菜单上选“部署”即可）。也可以使用[/size][/font][font=&quot][size=10.5pt]SQL[/size][/font][font=宋体][size=10.5pt]语句来部署。假设上面的程序生成的[/size][/font][font=&quot][size=10.5pt]dll[/size][/font][font=宋体][size=10.5pt]为[/size][/font][font=&quot][size=10.5pt]MyAggregate.dll[/size][/font][font=宋体][size=10.5pt]，可以使用下面的[/size][/font][font=&quot][size=10.5pt]SQL[/size][/font][font=宋体][size=10.5pt]语句来部署：[/size][/font]
[color=Black]CREATE ASSEMBLY MyAgg FROM 'D:        estMyAggregate.dll'

CREATE AGGREGATE joinstr (@input nvarchar([b]200[/b])) RETURNS nvarchar(max)
EXTERNAL NAME MyAgg.joinstr
[/color]

[color=Black]    要注意的是，字符串类型需要用nvarchar，而不能用varchar。[/color]
[font=宋体]第一条[/font]SQL[font=宋体]语句是装载[/font]dll[font=宋体]，第二条[/font]SQL[font=宋体]语句是注册[/font]joinstr[font=宋体]聚合函数（每一个[/font]C#[font=宋体]类就是一个聚合函数）[/font]
[font=宋体][size=10.5pt]在执行上面的[/size][/font][font=&quot][size=10.5pt]SQL[/size][/font][font=宋体][size=10.5pt]语句之前，需要将[/size][/font][font=&quot][size=10.5pt]SQL Server2005[/size][/font][font=宋体][size=10.5pt]的[/size][/font][font=&quot][size=10.5pt]clr[/size][/font][font=宋体][size=10.5pt]功能打开。如图[/size][/font][font=&quot][size=10.5pt]9[/size][/font][font=宋体][size=10.5pt]所示。


[img]http://www.cnblogs.com/images/cnblogs_com/nokiaguy/clragg/09.jpg[/img]

                                               图9

    如果想删除上面建立的聚合函数，可以使用如下的[/size][/font][font=&quot][size=10.5pt]SQL[/size][/font][font=宋体][size=10.5pt]语句：

[/size][/font][color=Black]drop aggregate joinstr[/color]

[font=宋体]在删除聚合函数后，可以将[/font]MyAggregate.dll[font=宋体]卸载。[/font]
[color=Black]drop assembly MyAgg[/color]
OK[font=宋体]，现在可以使用[/font]joinstr[font=宋体]来聚合字符串了。[/font]
[font=宋体]这种方法虽然显示很“酷”，但却要求开发人员熟悉扩展聚合函数的开发方法，如果开发人员使有的不是微软的开发工具，如使用[/font]Java[font=宋体]，恐怕这种方法就只能是空谈了（除非开发小组内有人会用微软的开发工具）。[/font]
[font=宋体]当然，如果使用其他的数据库，如[/font]oracle[font=宋体]、[/font]mysql[font=宋体]，也是可以实现类似扩展函数的功能的，如[/font]oracle[font=宋体]可以使用[/font]java[font=宋体]来进行扩展。但这要求开发人员具有更高的素质。[/font]
[font=宋体]以上介绍的三种方法仅供参考，至于采用哪种方法，可根据实际需要和具体情况而定。如果哪位读者有更好的方法，请跟贴！[/font]

[[i] 本帖最后由 银河使者 于 2008-6-27 20:22 编辑 [/i]]

great:right: ,原创的好

不过这种方法依赖性会不会太强了(oracle,db2,sql 2000能用不?),不适合产品中使用吧,搞个存储过程会不会更简单点?适合微软的人推销产品用,呵呵

一般建议使用第二种动态生成SQL的方法（这种方法几乎适合于所有数据库），第三种只适应于SQL Server2005及更高版本

恩,学习了:)

呵呵 感觉像微软的教科书:)