原表
pno type goods1 goods2
1 2 A C
1 2 B C
2 1 D E
3 3 F G
3 3 F H
3 3 F J我想转换成新表
pno type goods1 goods2
1 2 A,B C
2 1 D E
3 3 F G,H,J说明:就是按照pno分类,相同的pno,goods1,goods进行横向合并哪位能替我解难,小弟万分感谢
pno type goods1 goods2
1 2 A C
1 2 B C
2 1 D E
3 3 F G
3 3 F H
3 3 F J我想转换成新表
pno type goods1 goods2
1 2 A,B C
2 1 D E
3 3 F G,H,J说明:就是按照pno分类,相同的pno,goods1,goods进行横向合并哪位能替我解难,小弟万分感谢
returns nvarchar(100)
as
begin
declare @s nvarchar(100)
select @s=isnull(@s+',','')+goods1 from table1 where pno=@pno and type=@type group by goods1
return @s
endgo
create function test_f2(@pno int,@type int)
returns nvarchar(100)
as
begin
declare @s nvarchar(100)
select @s=isnull(@s+',','')+goods2 from table1 where pno=@pno and type=@type group by goods2
return @s
end
go
select distinct pno , type ,goods1=dbo.test_f(pno , type),goods2 =dbo.test_f2(pno , type) from table1
insert table1 select 1, 2, 'A', 'C'
union all select 1, 2, 'B', 'C'
union all select 2, 1, 'D', 'E'
union all select 3, 3, 'F', 'G'
union all select 3, 3, 'F', 'H'
union all select 3, 3, 'F', 'J'go
create function test_f(@pno int,@type int)
returns nvarchar(100)
as
begin
declare @s nvarchar(100)
select @s=isnull(@s+',','')+goods1 from table1 where pno=@pno and type=@type group by goods1
return @s
endgo
create function test_f2(@pno int,@type int)
returns nvarchar(100)
as
begin
declare @s nvarchar(100)
select @s=isnull(@s+',','')+goods2 from table1 where pno=@pno and type=@type group by goods2
return @s
end
goselect distinct pno , type ,goods1=dbo.test_f(pno , type),goods2 =dbo.test_f2(pno , type) from table1go
drop function test_f,test_f2pno type goods1 goods2
----------- ----------- -------------------- --------------------
1 2 A,B C
2 1 D E
3 3 F G,H,J(所影响的行数为 3 行)
union all select 1, 2, 'B', 'C'
union all select 2, 1, 'D', 'E'
union all select 3, 3, 'F', 'G'
union all select 3, 3, 'F', 'H'
union all select 3, 3, 'F', 'J'
请问上楼的朋友,这些代码是什么作用,这种写法能详细解释一下吗?
union all select 1, 2, 'B', 'C'
union all select 2, 1, 'D', 'E'
union all select 3, 3, 'F', 'G'
union all select 3, 3, 'F', 'H'
union all select 3, 3, 'F', 'J'
请问上楼的朋友,这些代码是什么作用,这种写法能详细解释一下吗?
--------------
这个就是在table1里插入你给的数据啊:
pno type goods1 goods2
1 2 A C
1 2 B C
2 1 D E
3 3 F G
3 3 F H
3 3 F J
插入表数据的一种写法
可用select /values
用select 时可+union all
create table b(pno int,type int,goods1 varchar(20),goods2 varchar(20))
insert b values(1,2,'A','C')
insert b values(1,2,'B','C')
insert b values(2,1,'D','E')
insert b values(3,3,'F','G')
insert b values(3,3,'F','H')
insert b values(3,3,'F','J')
go
create function f_hb(@pno char(10))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' +goods1 from (select distinct pno,goods1 from b where pno = pno) b where pno = @pno
set @str = right(@str , len(@str) - 1)
return(@str)
End
create function f_hb1(@pno char(10))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' +goods2 from (select distinct pno,goods2 from b where pno = pno) b where pno = @pno
set @str = right(@str , len(@str) - 1)
return(@str)
End
select distinct pno,type,dbo.f_hb(pno) as goods1,dbo.f_hb1(pno) as goods2 from b
go
返回:
pno type goods1 goods2
----------- ----------- -------------------- --------------------
1 2 A,B C
2 1 D E
3 3 F G,H,J(所影响的行数为 3 行)