对数量金额求和并对某些字段的值用逗号合并(类似多行转为一行)

[pengalwin]

存储过程如何写？或者前台代码呢？

请问如何对数量金额求和并对某些字段的值用逗号合并(类似多行转为一行)，数据来自多表

对相同的车牌号，统计它的装货数量和货品单价，并且合并货品名称，如

ChePaiHao FaCheShiJian HuiCheShiJian HuoPingMingCheng DanJia ShuLiang
粤K2541 2010-06-07 2010-07-02 大理石桌 150.00 20
粤K2541 2010-06-07 2010-07-02 大理石凳 135.00 50
粤K2369 2010-06-11 null 衣柜 145.00 22
粤K2369 2010-06-11 null 木床 100.00 13

我要的结果是：
ChePaiHao FaCheShiJian HuiCheShiJian HuoPingMingCheng DanJia ShuLiang
粤K2541 2010-06-07 2010-07-02 大理石桌，大理石凳 285.00 70
粤K2541 2010-06-11 null 衣柜 ，木床 245.00 35

即对相同的车牌，对货品的单价和数量求和，对相同字段不同值的合并成一个串。如何解决？！

请高手出招指点！！谢谢！！！

[peng_yaoming]

2楼行啊，就是那样的，如果你的是多表，相对麻烦d，修改下就是了

[WTPMCheng]

没有弄过！！

纯帮顶了。

[SQL77]

CREATE   FUNCTION GET_STRING(@NAME VARCHAR(10))
RETURNS NVARCHAR(40)
AS
 BEGIN
     DECLARE @RESULT  NVARCHAR(40)
	SELECT @RESULT=ISNULL(@RESULT+',','')+PATTERN+'('+LTRIM(COUNT)+')' FROM A WHERE NAME=@NAME
     RETURN @RESULT
END

可以使用SQL自定函数,
SQL2005以上就可以使用STUFF FOR XML

[htl258_Tony]

----------------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date   : 2010-07-10 10:02:44
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
--          Jul  9 2008 14:43:34 
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
-- Blog   : http://blog.csdn.net/htl258
----------------------------------------------------------------------------------

--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
	DROP TABLE [tb]
GO
CREATE TABLE [tb] ([ChePaiHao] [nvarchar](10),[FaCheShiJian] [datetime],[HuiCheShiJian] [datetime],[HuoPingMingCheng] [nvarchar](10),[DanJia] [numeric](5,2),[ShuLiang] [int])
INSERT INTO [tb]
SELECT '粤K2541','2010-06-07','2010-07-02','大理石桌','150.00','20' UNION ALL
SELECT '粤K2541','2010-06-07','2010-07-02','大理石凳','135.00','50' UNION ALL
SELECT '粤K2369','2010-06-11',NULL,'衣柜','145.00','22' UNION ALL
SELECT '粤K2369','2010-06-11',NULL,'木床','100.00','13'

--SELECT * FROM [tb]

-->SQL查询如下:
SELECT ChePaiHao,CONVERT(CHAR,FaCheShiJian,23) FaCheShiJian,CONVERT(CHAR,HuiCheShiJian,23) HuiCheShiJian,
	HuoPingMingCheng = STUFF((SELECT ','+HuoPingMingCheng FROM tb WHERE ChePaiHao=t.ChePaiHao FOR XML PATH('')),1,1,''),
	DanJia=SUM(DanJia),
	ShuLiang=SUM(ShuLiang)
FROM tb t
GROUP BY ChePaiHao,CONVERT(CHAR,FaCheShiJian,23),CONVERT(CHAR,HuiCheShiJian,23)
ORDER BY 2
/*
ChePaiHao  FaCheShiJian                   HuiCheShiJian                  HuoPingMingCheng           DanJia                                  ShuLiang
---------- ------------------------------ ------------------------------ ----------------------- --------------------------------------- -----------
粤K2541     2010-06-07                     2010-07-02                     大理石桌,大理石凳         285.00                                  70
粤K2369     2010-06-11                     NULL                           衣柜,木床                245.00                                  35

(2 行受影响)
*/