现在我根据合同号查询到了这个合同所采用的工艺和对应的工序
(注:一个工艺对应多个工序)
68F5691775774F35BB52480B2E576385:是"雕刻"工艺
F8F1C45B3AD24061AB7B6B167DA5CDE7:是"吊锣"工艺工序(Caption) 工艺(Craftwork)下料 68F5691775774F35BB52480B2E576385
雕刻 68F5691775774F35BB52480B2E576385
打磨 68F5691775774F35BB52480B2E576385
排钻 68F5691775774F35BB52480B2E576385
覆膜 68F5691775774F35BB52480B2E576385
清洁 68F5691775774F35BB52480B2E576385
清料 68F5691775774F35BB52480B2E576385
下料 F8F1C45B3AD24061AB7B6B167DA5CDE7
吊锣 F8F1C45B3AD24061AB7B6B167DA5CDE7
排钻 F8F1C45B3AD24061AB7B6B167DA5CDE7
打磨 F8F1C45B3AD24061AB7B6B167DA5CDE7
覆膜 F8F1C45B3AD24061AB7B6B167DA5CDE7
清洁 F8F1C45B3AD24061AB7B6B167DA5CDE7
清料 F8F1C45B3AD24061AB7B6B167DA5CDE7现在我要把这么多条记录根据不同的工艺所对应的工序分别分为一条记录如:"雕刻"工艺应显示为:
CraftWork caption
68F5691775774F35BB52480B2E576385 下料,雕刻,打磨,排钻,覆膜,清洁,清料同理"吊锣"工艺
CraftWork caption
F8F1C45B3AD24061AB7B6B167DA5CDE7 下料,吊锣,排钻,打磨,覆膜,清洁,清料请问该如何实现?谢谢
(注:一个工艺对应多个工序)
68F5691775774F35BB52480B2E576385:是"雕刻"工艺
F8F1C45B3AD24061AB7B6B167DA5CDE7:是"吊锣"工艺工序(Caption) 工艺(Craftwork)下料 68F5691775774F35BB52480B2E576385
雕刻 68F5691775774F35BB52480B2E576385
打磨 68F5691775774F35BB52480B2E576385
排钻 68F5691775774F35BB52480B2E576385
覆膜 68F5691775774F35BB52480B2E576385
清洁 68F5691775774F35BB52480B2E576385
清料 68F5691775774F35BB52480B2E576385
下料 F8F1C45B3AD24061AB7B6B167DA5CDE7
吊锣 F8F1C45B3AD24061AB7B6B167DA5CDE7
排钻 F8F1C45B3AD24061AB7B6B167DA5CDE7
打磨 F8F1C45B3AD24061AB7B6B167DA5CDE7
覆膜 F8F1C45B3AD24061AB7B6B167DA5CDE7
清洁 F8F1C45B3AD24061AB7B6B167DA5CDE7
清料 F8F1C45B3AD24061AB7B6B167DA5CDE7现在我要把这么多条记录根据不同的工艺所对应的工序分别分为一条记录如:"雕刻"工艺应显示为:
CraftWork caption
68F5691775774F35BB52480B2E576385 下料,雕刻,打磨,排钻,覆膜,清洁,清料同理"吊锣"工艺
CraftWork caption
F8F1C45B3AD24061AB7B6B167DA5CDE7 下料,吊锣,排钻,打磨,覆膜,清洁,清料请问该如何实现?谢谢
drop table tbTest
if object_id('fnMerge') is not null
drop function fnMerge
GO
create table tbTest(Caption varchar(10),Craftwork varchar(50))
insert tbTest
select '下料', '68F5691775774F35BB52480B2E576385' union all
select '雕刻', '68F5691775774F35BB52480B2E576385' union all
select '打磨', '68F5691775774F35BB52480B2E576385' union all
select '排钻', '68F5691775774F35BB52480B2E576385' union all
select '覆膜', '68F5691775774F35BB52480B2E576385' union all
select '清洁', '68F5691775774F35BB52480B2E576385' union all
select '清料', '68F5691775774F35BB52480B2E576385' union all
select '下料', 'F8F1C45B3AD24061AB7B6B167DA5CDE7' union all
select '吊锣', 'F8F1C45B3AD24061AB7B6B167DA5CDE7' union all
select '排钻', 'F8F1C45B3AD24061AB7B6B167DA5CDE7' union all
select '打磨', 'F8F1C45B3AD24061AB7B6B167DA5CDE7' union all
select '覆膜', 'F8F1C45B3AD24061AB7B6B167DA5CDE7' union all
select '清洁', 'F8F1C45B3AD24061AB7B6B167DA5CDE7' union all
select '清料', 'F8F1C45B3AD24061AB7B6B167DA5CDE7'
GO
create function fnMerge(@Craftwork varchar(50))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' + Caption from tbTest where Craftwork = @Craftwork
return stuff(@str,1,1,'')
end
GO
----查询
select Craftwork,dbo.fnMerge(Craftwork) as Caption
from tbTest group by Craftworkdrop table tbTest
drop function fnMerge/*结果
Craftwork Caption
-------------------------------------------------- ------------------
68F5691775774F35BB52480B2E576385 下料,雕刻,打磨,排钻,覆膜,清洁,清料
F8F1C45B3AD24061AB7B6B167DA5CDE7 下料,吊锣,排钻,打磨,覆膜,清洁,清料
*/
go
create table tbtest(Caption varchar(10),Craftwork varchar(50))
insert into tbtest
select '下料','68F5691775774F35BB52480B2E576385'
union all select '雕刻','68F5691775774F35BB52480B2E576385'
union all select '打磨','68F5691775774F35BB52480B2E576385'
union all select '排钻','68F5691775774F35BB52480B2E576385'
union all select '覆膜','68F5691775774F35BB52480B2E576385'
union all select '清洁','68F5691775774F35BB52480B2E576385'
union all select '清料','68F5691775774F35BB52480B2E576385'
union all select '下料','F8F1C45B3AD24061AB7B6B167DA5CDE7'
union all select '吊锣','F8F1C45B3AD24061AB7B6B167DA5CDE7'
union all select '排钻','F8F1C45B3AD24061AB7B6B167DA5CDE7'
union all select '打磨','F8F1C45B3AD24061AB7B6B167DA5CDE7'
union all select '覆膜','F8F1C45B3AD24061AB7B6B167DA5CDE7'
union all select '清洁','F8F1C45B3AD24061AB7B6B167DA5CDE7'
union all select '清料','F8F1C45B3AD24061AB7B6B167DA5CDE7'drop function uf_getstr
go
create function uf_getstr(@Craftwork varchar(50))
returns varchar(1000)
as
begin
declare @str varchar(1000)
set @str=''
select @str=@str+','+Caption from tbtest where Craftwork=@Craftwork
set @str=stuff(@str,1,1,'')
return @str
end
GO
select distinct Craftwork,dbo.uf_getstr(Craftwork) as caption from tbtest
/*
Craftwork caption
-------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
68F5691775774F35BB52480B2E576385 下料,雕刻,打磨,排钻,覆膜,清洁,清料
F8F1C45B3AD24061AB7B6B167DA5CDE7 下料,吊锣,排钻,打磨,覆膜,清洁,清料(所影响的行数为 2 行)
*/
--如果有重复数据行时你的结果就会有问题
insert into tbtest
select '下料','68F5691775774F35BB52480B2E576385'
insert into tbtest
select '下料','68F5691775774F35BB52480B2E576385'
insert tbTest
select '下料', '68F5691775774F35BB52480B2E576385' union all
select '下料', '68F5691775774F35BB52480B2E576385' union all
select '雕刻', '68F5691775774F35BB52480B2E576385' union all
select '打磨', '68F5691775774F35BB52480B2E576385' union all
select '排钻', '68F5691775774F35BB52480B2E576385' union all
select '覆膜', '68F5691775774F35BB52480B2E576385' union all
select '清洁', '68F5691775774F35BB52480B2E576385' union all
select '清料', '68F5691775774F35BB52480B2E576385' union all
select '下料', 'F8F1C45B3AD24061AB7B6B167DA5CDE7' union all
select '吊锣', 'F8F1C45B3AD24061AB7B6B167DA5CDE7' union all
select '排钻', 'F8F1C45B3AD24061AB7B6B167DA5CDE7' union all
select '打磨', 'F8F1C45B3AD24061AB7B6B167DA5CDE7' union all
select '覆膜', 'F8F1C45B3AD24061AB7B6B167DA5CDE7' union all
select '清洁', 'F8F1C45B3AD24061AB7B6B167DA5CDE7' union all
select '清料', 'F8F1C45B3AD24061AB7B6B167DA5CDE7'
GO
create function dbo.f_print(@Craftwork varchar(50))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' + Caption from (
select Craftwork,Caption from tbTest group by Craftwork,Caption) a where Craftwork = @Craftwork
return stuff(@str,1,1,'')
end
GOselect Craftwork,dbo.f_print(Craftwork) as Caption
from tbTest group by Craftwork
如果有重复值,可以在函数体中进行直接分组,而不必用子查询.
select @str = @str + ',' + Caption from tbTest where Craftwork = @Craftwork
group by Caption /*加上此行*/
我的数据是随机读取的,按照你们这样的方法不可能实现
我的数据是随机读取的,按照你们这样的方法不可能实现
--------------------------------------------------------------------------------
呵呵,这是在创建测试数据...