求救

[Mirror然]

用SQL语句编写出来
表 A
pid pnum psid
1 19 001
1 29 002
2 1 001
2 43 002
3 43 003
用SQL 语言
pid psi1d psi2d psi3d
1 19 29 0
2 1 43 0
3 0 0 43
psid 是仓库ID

[Unending]

在企业管理器中手动创建表
右键点击表->所有任务->生成SQL脚本

[cloudgamer]

学习

[gahade]

drop table A
go
create table A(pid int,pnu int,psid varchar(10))
insert into A
select 1,19,'001'
union all select 1,29,'002'
union all select 2,1,'001'
union all select 2,43,'002'
union all select 3,43,'003'

declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',max(case when psid='''+psid+''' then pnu else 0 end) as '''+psid+''''
from (select distinct psid from A)t
exec('select pid'+@sql+' from A group by pid')
/*
pid 001 002 003
----------- ----------- ----------- -----------
1 19 29 0
2 1 43 0
3 0 0 43
*/

[gahade]

drop table A
go
create table A(pid int,pnu int,psid varchar(10))
insert into A
select 1,19,'001'
union all select 1,29,'002'
union all select 2,1,'001'
union all select 2,43,'002'
union all select 3,43,'003'

declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',max(case when psid='''+psid+''' then pnu else 0 end) as ''psi'+rtrim(cast(psid as int))+'d'''
from (select distinct psid from A)t
exec('select pid'+@sql+' from A group by pid')
/*
pid psi1d psi2d psi3d
----------- ----------- ----------- -----------
1 19 29 0
2 1 43 0
3 0 0 43
*/

[leo_lesley]

drop table A
go
create table A(pid int,pnum int,psid varchar(10))
insert into A
select 1,19,'001'
union all select 1,29,'002'
union all select 2,1,'001'
union all select 2,43,'002'
union all select 3,43,'003'

select pid,
psi1d=isnull(max(case when psid='001' then pnum end),0) ,
psi2d=isnull(max(case when psid='002' then pnum end),0) ,
psi3d=isnull(max(case when psid='003' then pnum end ),0)
from A
group by pid

[gahade]

To: leo_lesley(leo)
psid是多个值是就要改代码! :)

[leo_lesley]

是的,lz的貌似psid值固定，要是psid值不固定就得用你的 ；）