请问如何将以下数据
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
------解决方案--------------------
--try
create table T(col1 varchar(20), col2 varchar(20), col3 varchar(20), col4 varchar(20))
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 ', NULL
select
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)