mssql2005如何实现类似GROUP_CONCAT (my-sql) 的功能

[joper]

有一表结构如下

流水号 企业ID 订单日期 订单号
1 1 2008-01-01 no00001
2 1 2008-01-01 no00002
3 1 2008-01-01 no00003
4 1 2008-01-01 no00004
5 2 2008-01-01 no00005
6 2 2008-01-01 no00006
--------------------
要求如下做成如下效果(按日期和企业ID汇总)

企业ID 订单日期 订单号 订单数量
1 2008-01-01 no00001;no00002;no00003;no00004 4
2 2008-01-01 no00005;no00006 2
--------------------------

请问要如何做呢
请教各位

[liangCK]

问题描述：
无论是在sql 2000，还是在 sql 2005 中,都没有提供字符串的聚合函数，
  所以，当我们在处理下列要求时，会比较麻烦：
有表tb, 如下：
id    value
----- ------
1     aa
1     bb
2     aaa
2     bbb
2     ccc
需要得到结果：
id     values
------ -----------
1      aa,bb
2      aaa,bbb,ccc
即， group by id, 求 value 的和（字符串相加）

1. 旧的解决方法

-- 1. 创建处理函数
CREATE FUNCTION dbo.f_str(@id int)
RETURNS varchar(8000)
AS
BEGIN
    DECLARE @r varchar(8000)
    SET @r = ''
    SELECT @r = @r + ',' + value
    FROM tb
    WHERE id=@id
    RETURN STUFF(@r, 1, 1, '')
END
GO
-- 调用函数

SELECt id, values=dbo.f_str(id) 
FROM tb 
GROUP BY id

-- 2. 新的解决方法 
-- 示例数据
DECLARE @t TABLE(id int, value varchar(10))
INSERT @t SELECT 1, 'aa'
UNION ALL SELECT 1, 'bb'
UNION ALL SELECT 2, 'aaa'
UNION ALL SELECT 2, 'bbb'
UNION ALL SELECT 2, 'ccc'

-- 查询处理
SELECT *
FROM(
    SELECT DISTINCT 
        id
    FROM @t
)A
OUTER APPLY(
    SELECT 
        [values]= STUFF(REPLACE(REPLACE(
            (
                SELECT value FROM @t N
                WHERE id = A.id
                FOR XML AUTO
            ), '<N value="', ','), '"/>', ''), 1, 1, '')
)N

/*--结果
id          values
----------- ----------------
1           aa,bb
2           aaa,bbb,ccc
(2 行受影响)
--*/

CSDN 社区帖子地址 

附: 合并与分拆的CLR， sql2005的示例中有：
在安装sql 2005的示例后，默认安装目录为
drive:\Program Files\Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\StringUtilities中

[joper]

如果是二个字段都要这样汇总呢


还有就是如何汇总订单数量呢

[M1CR0S0FT]

动下脑袋呗~

[fhtcgym]

2楼真强，for xml auto 真是好用

[Andy-W]

--> --> (Andy)生成测试数据 2008-08-20
Set Nocount On
declare @1 table([流水号] int,[企业ID] int,[订单日期] Datetime,[订单号] nvarchar(7))
Insert @1
select 1,1,'2008-01-01',N'no00001' union all
select 2,1,'2008-01-01',N'no00002' union all
select 3,1,'2008-01-01',N'no00003' union all
select 4,1,'2008-01-01',N'no00004' union all
select 5,2,'2008-01-01',N'no00005' union all
select 6,2,'2008-01-01',N'no00006'

Select [企业ID],[订单日期],[订单号],[订单数量]
From (Select [企业ID],[订单日期],[订单数量]=Count(*)
From @1
Group By [企业ID],[订单日期]) a
Outer Apply(Select [订单号]=Stuff((Select ','+[订单号]
From @1
Where [企业ID]=a.[企业ID] And [订单日期]=a.[订单日期] For Xml Path('')
),1,1,'')
) p

/*
企业ID 订单日期 订单号 订单数量
-----------------------------------------------------------------------------
1 2008-01-01 00:00:00.000 no00001,no00002,no00003,no00004 4
2 2008-01-01 00:00:00.000 no00005,no00006 2
*/

[joper]

[Quote=引用 5 楼 DVD_01 的回复:]
Select [企业ID],[订单日期],[订单号],[订单数量]
From (Select [企业ID],[订单日期],[订单数量]=Count(*)
From @1
Group By [企业ID],[订单日期]) a
Outer Apply(Select [订单号]=Stuff((Select ','+[订单号]
From @1
Where [企业ID]=a.[企业ID] And [订单日期]=a.[订单日期] For Xml Path('')
),1,1,'')
) p
[/Quote]
非常谢谢你的回答,谢谢!
再想问一下,如果除了[订单号]还有一个[票据号]也需要作这样的处理,
Outer Apply(Select [订单号]=Stuff((Select ','+[订单号]
From @1
Where [企业ID]=a.[企业ID] And [订单日期]=a.[订单日期] For Xml Path('')
),1,1,'')
) p 这句话应该如何修改,我改了几次都提示出错,不清楚格式是如何的,可否再请教

[joper]

已完美解决,谢谢楼主各位

谢谢

------------------------------
但是这种查询不能变成视图,如果想将结果保留起来进行第二次查询的话要如何做呢