当前位置: 代码迷 >> Sql Server >> SQL查询合并查询列的有关问题
  详细解决方案

SQL查询合并查询列的有关问题

热度:75   发布时间:2016-04-27 12:15:21.0
SQL查询合并查询列的问题

假设我有一个表是上图的样子,我想实现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
  相关解决方案