--我写的方法,超级麻烦。看看高手怎么做吧
--测试数据
create table ta(c1 varchar(10), c2 varchar(10), c3 varchar(10), c4 varchar(10),
c5 varchar(10), c6 varchar(10), c7 varchar(10), c8 varchar(10))
insert ta select '001', '1', '1', null, null, null, null, null
union all select '001', '2', '2', null, null, null, null, null
union all select '001', null, null, '3', '3', null, null, null
union all select '001', null, null, null, null, '4', '4', '4'
union all select '001', null, null, null, null, '5', '5', '5'
union all select '001', null, null, null, null, 'a', 'a', 'a'
union all select '002', '8', '8', null, null, null, null, null
union all select '002', null, null, '6', '6', null, null, null
union all select '002', null, null, '9', '9', null, null, null
union all select '002', null, null, null, null, '7', '7', '7'
--处理
select id=identity(int), * into #tt
from ta
order by c1, c2 , c3, c4, c5, c6, c7,c8
--创建临时表
select id=1+id-(select min(id) from #tt as t2
where c2 is not null and t1.c1=t2.c1)
, c1, c2
into #t2
from #tt as t1 where c2 is not nullselect id=1+id-(select min(id) from #tt as t2
where c3 is not null and t1.c1=t2.c1)
, c1, c3
into #t3
from #tt as t1 where c3 is not null
select id=1+id-(select min(id) from #tt as t2
where c4 is not null and t1.c1=t2.c1)
, c1, c4
into #t4
from #tt as t1 where c4 is not null
select id=1+id-(select min(id) from #tt as t2
where c5 is not null and t1.c1=t2.c1)
, c1, c5
into #t5
from #tt as t1 where c5 is not nullselect id=1+id-(select min(id) from #tt as t2
where c6 is not null and t1.c1=t2.c1)
, c1, c6
into #t6
from #tt as t1 where c6 is not null
select id=1+id-(select min(id) from #tt as t2
where c7 is not null and t1.c1=t2.c1)
, c1, c7
into #t7
from #tt as t1 where c7 is not null
select id=1+id-(select min(id) from #tt as t2
where c8 is not null and t1.c1=t2.c1)
, c1, c8
into #t8
from #tt as t1 where c8 is not null
--查询
select id=COALESCE(#t2.id, #t3.id, #t4.id, #t5.id, #t6.id, #t7.id, #t8.id),
c1=COALESCE(#t2.c1, #t3.c1, #t4.c1, #t5.c1, #t6.c1, #t7.c1, #t8.c1),
#t2.c2, #t3.c3, #t4.c4, #t5.c5, #t6.c6, #t7.c7, #t8.c8
into #result
from #t2 full join #t3 on #t2.c1=#t3.c1 and #t2.id=#t3.id
full join #t4 on #t3.c1=#t4.c1 and #t3.id=#t4.id
full join #t5 on #t4.c1=#t5.c1 and #t4.id=#t5.id
full join #t6 on #t5.c1=#t6.c1 and #t5.id=#t6.id
full join #t7 on #t6.c1=#t7.c1 and #t6.id=#t7.id
full join #t8 on #t7.c1=#t8.c1 and #t7.id=#t8.id
select c1, c2=max(c2), c3=max(c3), c4=max(c4), c5=max(c5)
, c6=max(c6), c7=max(c7), c8=max(c8)
from #result
group by id, c1
--清除
drop table #tt
drop table #t2
drop table #t3
drop table #t4
drop table #t5
drop table #t6
drop table #t7
drop table #t8
drop table #result
drop table ta
--测试数据
create table ta(c1 varchar(10), c2 varchar(10), c3 varchar(10), c4 varchar(10),
c5 varchar(10), c6 varchar(10), c7 varchar(10), c8 varchar(10))
insert ta select '001', '1', '1', null, null, null, null, null
union all select '001', '2', '2', null, null, null, null, null
union all select '001', null, null, '3', '3', null, null, null
union all select '001', null, null, null, null, '4', '4', '4'
union all select '001', null, null, null, null, '5', '5', '5'
union all select '001', null, null, null, null, 'a', 'a', 'a'
union all select '002', '8', '8', null, null, null, null, null
union all select '002', null, null, '6', '6', null, null, null
union all select '002', null, null, '9', '9', null, null, null
union all select '002', null, null, null, null, '7', '7', '7'
--处理
select id=identity(int), * into #tt
from ta
order by c1, c2 , c3, c4, c5, c6, c7,c8
--创建临时表
select id=1+id-(select min(id) from #tt as t2
where c2 is not null and t1.c1=t2.c1)
, c1, c2
into #t2
from #tt as t1 where c2 is not nullselect id=1+id-(select min(id) from #tt as t2
where c3 is not null and t1.c1=t2.c1)
, c1, c3
into #t3
from #tt as t1 where c3 is not null
select id=1+id-(select min(id) from #tt as t2
where c4 is not null and t1.c1=t2.c1)
, c1, c4
into #t4
from #tt as t1 where c4 is not null
select id=1+id-(select min(id) from #tt as t2
where c5 is not null and t1.c1=t2.c1)
, c1, c5
into #t5
from #tt as t1 where c5 is not nullselect id=1+id-(select min(id) from #tt as t2
where c6 is not null and t1.c1=t2.c1)
, c1, c6
into #t6
from #tt as t1 where c6 is not null
select id=1+id-(select min(id) from #tt as t2
where c7 is not null and t1.c1=t2.c1)
, c1, c7
into #t7
from #tt as t1 where c7 is not null
select id=1+id-(select min(id) from #tt as t2
where c8 is not null and t1.c1=t2.c1)
, c1, c8
into #t8
from #tt as t1 where c8 is not null
--查询
select id=COALESCE(#t2.id, #t3.id, #t4.id, #t5.id, #t6.id, #t7.id, #t8.id),
c1=COALESCE(#t2.c1, #t3.c1, #t4.c1, #t5.c1, #t6.c1, #t7.c1, #t8.c1),
#t2.c2, #t3.c3, #t4.c4, #t5.c5, #t6.c6, #t7.c7, #t8.c8
into #result
from #t2 full join #t3 on #t2.c1=#t3.c1 and #t2.id=#t3.id
full join #t4 on #t3.c1=#t4.c1 and #t3.id=#t4.id
full join #t5 on #t4.c1=#t5.c1 and #t4.id=#t5.id
full join #t6 on #t5.c1=#t6.c1 and #t5.id=#t6.id
full join #t7 on #t6.c1=#t7.c1 and #t6.id=#t7.id
full join #t8 on #t7.c1=#t8.c1 and #t7.id=#t8.id
select c1, c2=max(c2), c3=max(c3), c4=max(c4), c5=max(c5)
, c6=max(c6), c7=max(c7), c8=max(c8)
from #result
group by id, c1
--清除
drop table #tt
drop table #t2
drop table #t3
drop table #t4
drop table #t5
drop table #t6
drop table #t7
drop table #t8
drop table #result
drop table ta
from (select DISTINCT F1 as F1
from t) t1
left OUTER JOIN
(select F1, F2
from t
where F3 not is null) t2 on t2.F1 = t1.F1
left OUTER JOIN
(select F1, F3
from t
where F3 not is null) t3 on t3.F3 = t1.F1
left OUTER JOIN
(select F1, F4
from t
where F4 not is null) t4 on t4.F4 = t1.F1
left OUTER JOIN
(select F1, F5
from t
where F5 not is null) t5 on t5.F5 = t1.F1
left OUTER JOIN
(select F1, F6
from t
where F6 not is null) t6 on t6.F6 = t1.F1
left OUTER JOIN
(select F1, F7
from t
where F7 not is null) t7 on t7.F7 = t1.F1
left OUTER JOIN
(select F1, F8
from t
where F8 not is null) t8 on t8.F8 = t1.F1
left OUTER JOIN
(select F1, F9
from t
where F9 not is null) t9 on t9.F9 = t1.F1
order by t1.F1
--
create table #ta(c1 varchar(10), c2 varchar(10), c3 varchar(10), c4 varchar(10),
c5 varchar(10), c6 varchar(10), c7 varchar(10), c8 varchar(10))
insert #ta select '001', '1', '1', null, null, null, null, null
union all select '001', '2', '2', null, null, null, null, null
union all select '001', null, null, '3', '3', null, null, null
union all select '001', null, null, null, null, '4', '4', '4'
union all select '001', null, null, null, null, '5', '5', '5'
union all select '001', null, null, null, null, 'a', 'a', 'a'
union all select '002', '8', '8', null, null, null, null, null
union all select '002', null, null, '6', '6', null, null, null
union all select '002', null, null, '9', '9', null, null, null
union all select '002', null, null, null, null, '7', '7', '7'
go
select identity(int,1,1) as sn,c1,c2,c3,'9999' sn1
into #ta1
from
#ta where c2 is not null and c3 is not nullselect identity(int,1,1) as sn,c1,c4,c5,'9999' sn1
into #ta2
from
#ta where c4 is not null and c5 is not nullselect identity(int,1,1) as sn,c1,c6,c7,c8,'9999' as sn1
into #ta3
from
#ta where c6 is not null and c7 is not null and c8 is not null
goupdate #ta1
set sn1 = (SELECT COUNT(1) FROM #ta1 TA1 WHERE TA2.SN >= TA1.SN AND TA2.C1 = TA1.C1
group by Ta2.c1)
from #Ta1 Ta2
update #ta2
set sn1 = (SELECT COUNT(1) FROM #ta2 TA1 WHERE TA2.SN >= TA1.SN AND TA2.C1 = TA1.C1
group by Ta2.c1)
from #Ta2 Ta2update #ta3
set sn1 = (SELECT COUNT(1) FROM #ta3 TA1 WHERE TA2.SN >= TA1.SN AND TA2.C1 = TA1.C1
group by Ta2.c1)
from #Ta3 Ta2
go
select c1=case when #ta1.c1 is not null then #ta1.c1
when #ta2.c1 is not null then #ta2.c1
else #ta3.c1
end,c2=
isnull(c2,''),c3=isnull(c3,''),c4=isnull(c4,''),c5=isnull(c5,''),c6=isnull(c6,''),c7=isnull(c7,''),c8=isnull(c8,'') from #ta1 --,c6,c7,c8
full join #ta2 on
#ta1.c1 = #ta2.c1
and #ta1.sn1 = #ta2.sn1
full join #ta3 on
#ta3.c1 = #ta1.c1
and #ta3.sn1 = #ta1.sn1
order by c1/*(10 row(s) affected)
(3 row(s) affected)
(3 row(s) affected)
(4 row(s) affected)
(3 row(s) affected)
(3 row(s) affected)
(4 row(s) affected)c1 c2 c3 c4 c5 c6 c7 c8
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
001 1 1 3 3 4 4 4
001 2 2 5 5 5
001 a a a
002 8 8 6 6 7 7 7
002 9 9 (5 row(s) affected)
*/