假设我有一个表是上图的样子,我想实现group by on_tick_sellnumber_str 的效果,就是说按找这个列来合并最后两列的产品及数量,合并后的样子应是下面的样子,请大家指点一下有没有好的办法呢
on_tick_sellnumber_str detail(合并后的列名)
NH-AX000020 精选年货【5】;特色年货【27】
------解决方案--------------------
- SQL code
/*标题:按某字段合并字符串之一(简单合并)作者:爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)时间:2008-11-06地点:广东深圳描述:将如下形式的数据按id字段合并value字段。id value----- ------1 aa1 bb2 aaa2 bbb2 ccc需要得到结果:id value------ -----------1 aa,bb2 aaa,bbb,ccc即:group by id, 求 value 的和(字符串相加)*/--1、sql2000中只能用自定义的函数解决create table tb(id int, value varchar(10))insert into tb values(1, 'aa')insert into tb values(1, 'bb')insert into tb values(2, 'aaa')insert into tb values(2, 'bbb')insert into tb values(2, 'ccc')gocreate function dbo.f_str(@id int) returns varchar(100)asbegin declare @str varchar(1000) set @str = '' select @str = @str + ',' + cast(value as varchar) from tb where id = @id set @str = right(@str , len(@str) - 1) return @strendgo--调用函数select id , value = dbo.f_str(id) from tb group by iddrop function dbo.f_strdrop table tb--2、sql2005中的方法create table tb(id int, value varchar(10))insert into tb values(1, 'aa')insert into tb values(1, 'bb')insert into tb values(2, 'aaa')insert into tb values(2, 'bbb')insert into tb values(2, 'ccc')goselect id, [value] = stuff((select ',' + [value] from tb t where id = tb.id for xml path('')) , 1 , 1 , '')from tbgroup by iddrop table tb--3、使用游标合并数据create table tb(id int, value varchar(10))insert into tb values(1, 'aa')insert into tb values(1, 'bb')insert into tb values(2, 'aaa')insert into tb values(2, 'bbb')insert into tb values(2, 'ccc')godeclare @t table(id int,value varchar(100))--定义结果集表变量--定义游标并进行合并处理declare my_cursor cursor local forselect id , value from tbdeclare @id_old int , @id int , @value varchar(10) , @s varchar(100)open my_cursorfetch my_cursor into @id , @valueselect @id_old = @id , @s=''while @@FETCH_STATUS = 0begin if @id = @id_old select @s = @s + ',' + cast(@value as varchar) else begin insert @t values(@id_old , stuff(@s,1,1,'')) select @s = ',' + cast(@value as varchar) , @id_old = @id end fetch my_cursor into @id , @valueENDinsert @t values(@id_old , stuff(@s,1,1,''))close my_cursordeallocate my_cursorselect * from @tdrop table tb
------解决方案--------------------
行列转换
select on_tick_sellnumber_str ,精选年货=sum(case 倒数第二列的列名 when '精品年货' then exp end),
特色年货=sum(case 倒数第二列的列名 when '特色年货' then exp end)
from tb
group by on_tick_sellnumber_str
------解决方案--------------------
http://blog.csdn.net/happy664618843/archive/2010/03/05/5350835.aspx 参考
------解决方案--------------------
- SQL code
create table on_tick( on_tick_sellnumber_str nvarchar(256), ticktype_name_des nvarchar(256), expr2 int)insert into on_tick values(N'NH-AX000020', N'精选年货', 5)insert into on_tick values(N'NH-AX000020', N'特色年货', 27)insert into on_tick values(N'NH-AX000021', N'精选年货', 1)insert into on_tick values(N'NH-AX000021', N'特色年货', 2)select on_tick_sellnumber_str,'精选年货'=sum(case ticktype_name_des when '精选年货' then expr2 end),'特色年货'=sum(case ticktype_name_des when '特色年货' then expr2 end)from on_tickgroup by on_tick_sellnumber_str
------解决方案--------------------
dawugui 给出的就很全面了。支持1楼。
------解决方案--------------------
- SQL code
应该是字符串的合并select on_tick_sellnumber_str , [ticketype_name_dec] = stuff((select ',' + [ticketype_name_dec] from tb t where on_tick_sellnumber_str = tb.on_tick_sellnumber_str for xml path('')) , 1 , 1 , '')from tbgroup by on_tick_sellnumber_str