sql 如何合并 行

Question

表数据如下，Date日期有重复，如何把相同日期的行进行合并？ 让相同日期的其他列的值相加？（因为Depart列数不确定，最好只根据[Date]把表中的其他列值合并相加） 参考以下方法合并了一列 http://blog.csdn.net/dobear_0922/article/details/2313839 sql表数据如下 --Table CREATE TABLE [dbo].[tb]( [DATE] [varchar](10) NULL, [DepartA] [varchar](30) NULL, [DepartB] [varchar](30) NULL, [DepartC] [varchar](30) NULL ) --日期，Date有重复，日期相同时需要合并行 insert into [tb] values('2012-01-01','jack','a','f') insert into [tb] values('2012-01-01','peter','b','g') insert into [tb] values('2012-01-02','chalim','c','h') insert into [tb] values('2012-01-02','kobe','d','i') insert into [tb] values('2012-01-03','jack','e','j') insert into [tb] values('2012-01-03','rick','m','k') --此方法只能合并一列，如何根据[Date]合并其他行？？？ SELECT [DATE], [DepartA]=STUFF((SELECT ','+[DepartA] FROM [tb] WHERE [DATE]=[tb].[DATE] FOR XML PATH('')), 1, 1, '') FROM [tb] GROUP BY [DATE] 或者是把数据拉出来以后放到DataTable中处理也行 谢谢！

Answer 1

@sirili: 请问，你这个动态是如何做的，列数是个数组，你是通过程序去调用这个存储过程去生成的？还是说，数据库处理这个动态列的。

Answer 2

SELECT DATE , DepartA=STUFF((SELECT ','+[DepartA] FROM [tb] WHERE [DATE]=a.[DATE] FOR XML PATH('')), 1, 1, '') , DepartB=STUFF((SELECT ','+[DepartB] FROM [tb] WHERE [DATE]=a.[DATE] FOR XML PATH('')), 1, 1, '') , DepartC=STUFF((SELECT ','+[DepartC] FROM [tb] WHERE [DATE]=a.[DATE] FOR XML PATH('')), 1, 1, '') FROM [tb] a GROUP BY [DATE]

Answer 3

转换成实体，操作起来就方便了

Answer 4

你的写法有点问题，改一下就可以了。 SELECT [DATE], [DepartA]=STUFF((SELECT ','+[DepartA] FROM [tb] WHERE [DATE]=a.[DATE] FOR XML PATH('')), 1, 1, '') , [DepartB]=STUFF((SELECT ','+[DepartB] FROM [tb] WHERE [DATE]=a.[DATE] FOR XML PATH('')), 1, 1, '') , [DepartC]=STUFF((SELECT ','+[DepartC] FROM [tb] WHERE [DATE]=a.[DATE] FOR XML PATH('')), 1, 1, '') FROM [tb] a GROUP BY [DATE]

Answer 5

因为部门列数不确定，所以还是在SQL里完成了，动态SQL语句 set @sql3=@sql3+',['+@ColName+']=STUFF((SELECT ' set @sql3=@sql3+ ''','''+'+' set @sql3=@sql3+' ['+@ColName+'] '+' FROM [#tab2] WHERE [DATE]=a.[DATE] FOR XML PATH('''')), 1, 1, '''') ' FETCH NEXT from @cur2 into @ColName