sql基础操作之字符串拼接

又一篇笔记，以备后用！！

     需求：

有表tb，表有两个字段，分别为：id，value。

表中存有数据如下：id        value

                         1         a

                         1         a1

                         1         a2

                         2         b

                         2         b1

                         3         c1

                         3         c2

     需要通过查询实现：1         a,a1,a2

                              2         b,b1

                              3         c1,c2

实现方法：

          1、sql2000 使用合并函数：

          create function f_tb(@id int)
returns varchar(8000)
as
begin
  declare @str varchar(8000)
  set @str = ''
  select @str = @str + ',' + cast(value as varchar) from tb where id = @id
  set @str = right(@str , len(@str) - 1)
  return(@str)
End
go

--调用自定义函数得到结果：
select distinct id ,dbo.f_tb(id) as value from t

          2、sql2005 处理方式如下：

SELECT * FROM(SELECT DISTINCT id FROM tb)A OUTER APPLY(
        SELECT [values]= STUFF(REPLACE(REPLACE(
            (
                SELECT value FROM tb N
                WHERE id = A.id
                FOR XML AUTO
            ), '<N value="', ','), '"/>', ''), 1, 1, '')
)N

参考： http://topic.csdn.net/u/20080410/15/88f6491b-d9fe-4bd1-a350-22b4d5c9d415.html    #3

 

 

                                                                                                       2008-09-05  (笔记)