表A Num 表B id A.id price name
1 1 1 100 a
2 1 100 b
2 3 2 200 c
4 2 300 d
5 2 500 e
......结果: Num price name
1 200 a,b
2 1000 c,d,e求这条sql语句 要 一条sql语句就能实现的(不知可否) 谢谢!
1 1 1 100 a
2 1 100 b
2 3 2 200 c
4 2 300 d
5 2 500 e
......结果: Num price name
1 200 a,b
2 1000 c,d,e求这条sql语句 要 一条sql语句就能实现的(不知可否) 谢谢!
在SQL Server2005中按列连接字符串的三种方法
http://www.cnblogs.com/nokiaguy/archive/2008/06/25/1229594.html
可以使用 PIVOT 和 UNPIVOT 关系运算符对表值表达式进行操作以获得另一个表。PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来转换表值表达式,并在必要时对最终输出中所需的任何其余的列值执行聚合。UNPIVOT 与 PIVOT 执行相反的操作,将表值表达式的列转换为列值。http://www.cnblogs.com/Nina-piaoye/archive/2008/07/30/1256527.htmlhttp://msdn.microsoft.com/zh-cn/library/ms177410.aspx
Test Table:JAY_TEST
ID NUMBER Y
PID NUMBER Y
PRICE NUMBER Y
NAME VARCHAR2(20) Y
select a.pid,b.price,a.name
from
(
select pID,max(substr((sys_connect_by_path(name,',')),2)) name
from (
select pid,name,
rownum rnum,
row_number() over(partition by pid order by pid) rn1
from jay_test
)
start with rn1=1
connect by rnum-1=prior rnum
group by pid
) A,
(
select pid,sum(price) price
from jay_test
group by pid
) B
where a.pid = b.pidResult:
1 1 200 a,b
2 2 1000 c,d,e
go
if object_id('dbo.table_A') is not null
drop table dbo.table_A
if object_id('dbo.table_B') is not null
drop table dbo.table_Bcreate table [dbo].[table_A]
(
num int
)
create table [dbo].[table_B]
(
id int not null primary key identity(1,1),
Aid int,
price float,
name varchar(100)
)insert into table_A (num) values (1)
insert into table_A (num) values (2)
insert into table_A (num) values (3)
insert into table_B (Aid,price,name) values (1,10,'a')
insert into table_B (Aid,price,name) values (1,20,'b')
insert into table_B (Aid,price,name) values (1,10.5,'c')
insert into table_B (Aid,price,name) values (2,10,'a2')
insert into table_B (Aid,price,name) values (2,40,'b2')
insert into table_B (Aid,price,name) values (2,1.05,'c2')
insert into table_B (Aid,price,name) values (3,10,'a3')
insert into table_B (Aid,price,name) values (3,10,'b3')
insert into table_B (Aid,price,name) values (3,10,'c3')
insert into table_B (Aid,price,name) values (1,10,'d')declare @num int,@price float,@name varchar(100)
declare @tmpid int,@count int,@tmpName varchar(max),@tmpPrice float
declare @y table(num int,price float,name varchar(max))
set @count=0
declare tmp cursor fast_forward for
select A.Num,B.price,B.name from table_A as A left join table_B as B on A.Num=B.Aid
open tmp
fetch next from tmp into @num,@price,@name
while @@fetch_status=0
begin
if(@count=0)
begin
set @tmpid=@num
set @tmpName=@name
set @tmpPrice=@price
set @count=@count+1
end
else
begin
if(@tmpid=@num)
begin
set @tmpPrice=@tmpPrice+@price
set @tmpName=@tmpName+','+@name
end
else
begin
insert into @y (Num,Price,name) values (@tmpid,@tmpPrice,@tmpName)
set @tmpPrice=@price
set @tmpName=@name
set @tmpid=@num
end
end
fetch next from tmp into @num,@price,@name
end
insert into @y (Num,Price,name) values (@tmpid,@tmpPrice,@tmpName)
close tmp
deallocate tmp
select * from @y