解决方案 »
- 媒体结构家族不正确
- 因foreign key 带有级联delete 或update无法创建触发器
- sql2000中如何一到时间就自动删除一个表中的纪录
- 如何在当前服务器上附加只有主数据文件(即只有*.MDF文件)的数据库!?
- 用SQL2000配置分发服务器时,得用服务器名称注册SQL Server,而不能用IP?
- 一个查询视图的问题,想了一上午,我真的不知道,在线等
- sql:一个关于改变字段值的问题
- 有谁试过用order by时候出现 死锁??
- 关于sqlserver问题!
- 怎么用 osql 执行从原数据库生成的脚本 XX.sql
- 求各位大大怎么检查插入的数据与中断存储过程中的动作??
- 大数据量插入问题
create table sfd_file
(sfd04 varchar(10), sfd05 varchar(10), sfd06 varchar(10))insert into sfd_file
select 'X9372-W', 'W11813MP', '01' union all
select 'X9372-W', 'W13024MF', '01' union all
select 'X9372-W', 'W15180BE', '02' union all
select 'X9372-W', 'W15431L', '02' union all
select 'X9372-W', 'W15431L1', '02' union all
select 'X9372-W', 'W52483EV', '03' union all
select 'X9372-W', 'W54079AC', '03' union all
select 'X9372-W', 'W54196K', '04'
select sfd04 '单号',
isnull([01],'') '01车间',
isnull([02],'') '02车间',
isnull([03],'') '03车间',
isnull([04],'') '04车间'
from (select sfd04,sfd05,sfd06,
row_number() over(partition by sfd04,sfd06 order by getdate()) 'rn'
from sfd_file) a
pivot(max(sfd05) for sfd06 in([01],[02],[03],[04])) p/*
单号 01车间 02车间 03车间 04车间
---------- ---------- ---------- ---------- ----------
X9372-W W11813MP W15180BE W52483EV W54196K
X9372-W W13024MF W15431L W54079AC
X9372-W W15431L1 (3 row(s) affected)
*/
as(
select sfd04,sfd05,sfd06, row_number()over(partition by sfd06) as num
from TEST
)select num,sfd04,
max(case when sfd06='01' then sfd05 else '' end) [01],
max(case when sfd06='02' then sfd05 else '' end) [02],
max(case when sfd06='03' then sfd05 else '' end) [03],
max(case when sfd06='04' then sfd05 else '' end) [04]
from sfd_file group by num,sfd04
into #tmp
from sfd_file
select num,sfd04,
max(case when sfd06='01' then sfd05 else '' end) [01],
max(case when sfd06='02' then sfd05 else '' end) [02],
max(case when sfd06='03' then sfd05 else '' end) [03],
max(case when sfd06='04' then sfd05 else '' end) [04]
from #tmp group by num,sfd04
num,sfd04,
max(case when sfd06='01' then sfd05 else '' end) [01],
max(case when sfd06='02' then sfd05 else '' end) [02],
max(case when sfd06='03' then sfd05 else '' end) [03],
max(case when sfd06='04' then sfd05 else '' end) [04]
from #tmp
group by num,sfd04
单号 01 02 03 04
X9372-W W13024MF W15431L W54079AC W54196K
(sfd04 varchar(10), sfd05 varchar(10), sfd06 varchar(10))
insert into sfd_file
select 'X9372-W', 'W11813MP', '01' union all
select 'X9372-W', 'W13024MF', '01' union all
select 'X9372-W', 'W15180BE', '02' union all
select 'X9372-W', 'W15431L', '02' union all
select 'X9372-W', 'W15431L1', '02' union all
select 'X9372-W', 'W52483EV', '03' union all
select 'X9372-W', 'W54079AC', '03' union all
select 'X9372-W', 'W54196K', '04'
select *,ID=identity(int,1,1) into #tb from sfd_fileselect
px,sfd04,
max(case when sfd06='01' then sfd05 else '' end) [01],
max(case when sfd06='02' then sfd05 else '' end) [02],
max(case when sfd06='03' then sfd05 else '' end) [03],
max(case when sfd06='04' then sfd05 else '' end) [04]
from ( select *,px=(select COUNT(1)+1 from #tb where sfd06=t.sfd06 and ID<t.id) from #tb t)t
group by
px,sfd04
drop table sfd_file,#tb
/*
px sfd04 01 02 03 04
----------- ---------- ---------- ---------- ---------- ----------
1 X9372-W W11813MP W15180BE W52483EV W54196K
2 X9372-W W13024MF W15431L W54079AC
3 X9372-W W15431L1 (3 行受影响)
*/
create table s_file
(sfd04 varchar(10), sfd05 varchar(10), sfd06 varchar(10))
delete from s_file
insert into s_file
select 'X9372-W', 'W11813MP', '01' union all
select 'X9372-W', 'W13024MF', '01' union all
select 'X9372-W', 'W15180BE', '02' union all
select 'X9372-W', 'W15431L', '02' union all
select 'X9372-W', 'W15431L1', '02' union all
select 'X9372-W', 'W52483EV', '04' union all
select 'X9371-W', 'W54079AC', '03' union all
select 'X9371-W', 'W52483EV', '04' union all
select 'X9371-W', 'W54079AC', '03' union all
select 'X9372-W', 'W54196K', '04'
drop table #tb
select *,ID=identity(int,1,1) into #tb from s_file
select
px,sfd04,
max(case when sfd06='01' then sfd05 else '' end) [01],
max(case when sfd06='02' then sfd05 else '' end) [02],
max(case when sfd06='03' then sfd05 else '' end) [03],
max(case when sfd06='04' then sfd05 else '' end) [04]
from ( select *,px=(select COUNT(1)+1 from #tb where sfd06=t.sfd06 and ID<t.id) from #tb t)t
group by
px,sfd04