表ABC中有字段ID ACC OLDIDID ACC OLDID
A0012 20
A0014 30
A0015 20 A0012
……………………………………
A0090 30 A0015
……………………………………
A0120 20 A0090
……………………………………
要的结果
ID ACC OLDID1 ACC OLDID2 ACC OLDID3 ACC
A0120 20 A0090 30 A0015 20 A0012 20
或者
A0012 20 A0015 20 A0090 30 A0120 20注:即将表ABC中只要对原始单据进行修改过的,将它拼接起来(还有其它修改的,示例仅举只一条).最大拼接不超过5次.
A0012 20
A0014 30
A0015 20 A0012
……………………………………
A0090 30 A0015
……………………………………
A0120 20 A0090
……………………………………
要的结果
ID ACC OLDID1 ACC OLDID2 ACC OLDID3 ACC
A0120 20 A0090 30 A0015 20 A0012 20
或者
A0012 20 A0015 20 A0090 30 A0120 20注:即将表ABC中只要对原始单据进行修改过的,将它拼接起来(还有其它修改的,示例仅举只一条).最大拼接不超过5次.
解决方案 »
- 如何使用SQL 事件探查器调试 Transact-SQL 语句和存储过程?
- sql server 2000服务管理器问题
- 如何从exec sp_who中取出我想要的记录,并kill,请大家帮忙!
- 求高手,用一条sql更新一个表中的多条语句
- 统计出现点问题
- 查询两个表,不知道如何写,请高手指点一二
- 50分,有劳各位了,关于列转行的复杂查询(不仅仅是转换)!
- 怎样向建立好的一个视图,插入数据!!!!!!急用~~~~~~~~~~~
- 这个同步怎么做???有一定难度!!!!!!
- 怎样用语句删除某表的所有外键(FK),和重新建立这些外键?
- group by 分组时,如果高效地统计每一组中某一个字段为null的行数
- 行版本控制问题 ALLOW_SNAPSHOT_ISOLATION
ID ACC OLDID1 ACC OLDID2 ACC OLDID3 ACC
--------------------------------------------
A0120 20 A0090 30 A0015 20 A0012 20
A0014 30
还是要单条数据:
ID ACC OLDID1 ACC OLDID2 ACC OLDID3 ACC
----------------------------------------------
A0120 20 A0090 30 A0015 20 A0012 20
你的上一贴,我看过了,单条数据来处理的话,好办!如果想处理成最上面的那种结果的话,就不太好办了...
insert into ABC values ('A0012', 20 ,null)
insert into ABC values ('A0014', 30 ,null)
insert into ABC values ('A0015', 20, 'A0012')
insert into ABC values ('A0090', 30 ,'A0015')
insert into ABC values ('A0120', 20 ,'A0090');with cte
as (select id,acc,oldid,
cid=case when oldid IS null then id else oldid end from ABC where oldid is null
union all
select b.id,b.acc,b.oldid,a.cid from cte a,(select id,acc,oldid,
cid=case when oldid IS null then id else oldid end from ABC ) b
where a.id=b.oldid
),
cte1 as
(select cast(ROW_NUMBER() over (partition by cid order by id) as nvarchar) tid,
'acc'+cast(ROW_NUMBER() over (partition by cid order by id) as nvarchar) pid,type=id,cid from cte
union all
select cast(ROW_NUMBER() over (partition by cid order by id) as nvarchar) tid,
'oldid'+cast(ROW_NUMBER() over (partition by cid order by id) as nvarchar) pid,
cast(acc as nvarchar),cid from cte)
--drop table temp
select * into temp from cte1 declare @sql varchar(8000)
select @sql = isnull(@sql+'],[', '') + pid from temp group by pid,tid order by tid,pid
set @sql = '[' + @sql + ']'
exec ('select * from (select pid,type,cid from temp) a pivot (max(type) for pid in (' + @sql + ') ) b')
insert into ABC values ('A0012', 20 ,null)
insert into ABC values ('A0014', 30 ,null)
insert into ABC values ('A0015', 20, 'A0012')
insert into ABC values ('A0090', 30 ,'A0015')
insert into ABC values ('A0120', 20 ,'A0090');with cte
as (select id,acc,oldid,
cid=case when oldid IS null then id else oldid end from ABC where oldid is null
union all
select b.id,b.acc,b.oldid,a.cid from cte a,(select id,acc,oldid,
cid=case when oldid IS null then id else oldid end from ABC ) b
where a.id=b.oldid
),
cte1 as
(select cast(ROW_NUMBER() over (partition by cid order by id) as nvarchar) tid,
'acc'+cast(ROW_NUMBER() over (partition by cid order by id) as nvarchar) pid,type=id,cid from cte
union all
select cast(ROW_NUMBER() over (partition by cid order by id) as nvarchar) tid,
'oldid'+cast(ROW_NUMBER() over (partition by cid order by id) as nvarchar) pid,
cast(acc as nvarchar),cid from cte)
--drop table temp
select * into temp from cte1 declare @sql varchar(8000)
select @sql = isnull(@sql+'],[', '') + pid from temp group by pid,tid order by tid,pid
set @sql = '[' + @sql + ']'
exec ('select * from (select pid,type,cid from temp) a pivot (max(type) for pid in (' + @sql + ') ) b')cid acc1 oldid1 acc2 oldid2 acc3 oldid3 acc4 oldid4
---------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
A0012 A0012 20 A0015 20 A0090 30 A0120 20
A0014 A0014 30 NULL NULL NULL NULL NULL NULL(2 行受影响)