数据处理
请问如何将以下数据
2007021111NULL666666NULL
200702114-5YNULLNULLNULL
200702114-5YNULL22222NULL
200702114-5YNULL2222222NULL
200702116 NULL88888NULL
转换成:
2007021111NULL666666NULL
200702114-5YNULLNULLNULL
200702116 NULL88888NULL
[解决办法]
--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)
