请问如何将以下数据
2007021111 NULL 666666 NULL
200702114-5Y NULL NULL NULL
200702114-5Y NULL 22222 NULL
200702114-5Y NULL 2222222 NULL
200702116 NULL 88888 NULL
转换成:
2007021111 NULL 666666 NULL
200702114-5Y NULL NULL NULL
200702116 NULL 88888 NULL
2007021111 NULL 666666 NULL
200702114-5Y NULL NULL NULL
200702114-5Y NULL 22222 NULL
200702114-5Y NULL 2222222 NULL
200702116 NULL 88888 NULL
转换成:
2007021111 NULL 666666 NULL
200702114-5Y NULL NULL NULL
200702116 NULL 88888 NULL
insert T select '2007021111', NULL, '666666', NULL
union all select '200702114-5Y', NULL, NULL, NULL
union all select '200702114-5Y', NULL, '22222', NULL
union all select '200702114-5Y', NULL, '2222222', NULL
union all select '200702116', NULL, '88888', NULLselect
col1,
col2=min(isnull(col2, '')),
col3=min(isnull(col3, '')),
col4=min(isnull(col4, ''))
from T
group by col1--result
col1 col2 col3 col4
-------------------- -------------------- -------------------- --------------------
2007021111 666666
200702114-5Y
200702116 88888 (3 row(s) affected)
200702114-5Y mm NULL 232
200702114-5Y gg 22222 777
200702114-5Y ghg 2222222 ooi
200702116 565 88888 776
200702114-3Y pp NULL 44
200702114-3Y ll 22222 543
200702114-3Y ww 2222222 ds3
转换成:
2007021111 dd 666666 aa
200702114-5Y mm NULL 232
200702116 565 88888 776
200702114-3Y pp NULL 44
200702114-5Y mm NULL 232
200702114-5Y gg 22222 777
200702114-5Y ghg 2222222 ooi
200702116 565 88888 776
200702114-3Y pp NULL 44
200702114-3Y ll 22222 543
200702114-3Y ww 2222222 ds3
转换成:(转换后第一列的值是唯一的,并以所有列数据显示出来)
2007021111 dd 666666 aa
200702114-5Y mm NULL 232
200702116 565 88888 776
200702114-3Y pp NULL 44
----------------------------
union all select '200702114-5Y', 'mm', NULL , '232'
union all select '200702114-5Y', 'gg', '22222', '777'
union all select '200702114-5Y', 'ghg', '2222222', 'ooi'
union all select '200702116', '565', '88888', '776'
union all select '200702114-3Y', 'pp', NULL , '44'
union all select '200702114-3Y', 'll', '22222', '543'
union all select '200702114-3Y', 'ww', '2222222', 'ds3'--前提是c2列没有重复记录(如有重复,可通过在表中添加identity列来实现,方法类似)
select *
from #t t
where c2 in(
select top 1 c2 from #t
where c1 = t.c1
order by c1)
create table #t(c1 varchar(20),c2 varchar(3),c3 varchar(10),c4 varchar(3))insert #t select '2007021111','dd' , '00000' , 'aa'
union all select '200702114-5Y', 'mm', NULL , '232'
union all select '200702114-5Y', 'gg', '22222', '777'
union all select '200702114-5Y', 'ghg', '2222222', 'ooi'
union all select '200702116', '565', '88888', '776'
union all select '200702114-3Y', 'pp', NULL , '44'
union all select '200702114-3Y', 'll', '22222', '543'
union all select '200702114-3Y', 'ww', '2222222', 'ds3'--前提是c2列没有重复记录(如有重复,可通过在表中添加identity列来实现,方法类似)
select *
from #t t
where c2 in(
select top 1 c2 from #t
where c1 = t.c1
order by c1)