如下: 1 lxd punggol singapore NULL 2 lxd punggol NULL singapore 2 shy beijing NULL my love 2 shy beijing china NULL要求结果: id str_name str_address str_national str_desc 1 lxd punggol singapore singapore 2 shy beijing china my love如何搞,最好是用一条语句弄出来。谢谢。
insert into tb select 1,'lxd','punggol','singapore',NULL
insert into tb select 2,'lxd','punggol',NULL,'singapore'
insert into tb select 2,'shy','beijing',NULL,'my love'
insert into tb select 2,'shy','beijing','china',NULL
go
select id,
max(str_name)str_name,
min(str_address)str_address,
max(str_national)str_national,
(case when min(str_desc) is null then max(str_national) else min(str_desc) end) str_desc
from tb group by id
/*
id str_name str_address str_national str_desc
----------- -------------------- -------------------- -------------------- --------------------
1 lxd punggol singapore singapore
2 shy beijing china my love
警告: 聚合或其他 SET 操作消除了空值。(2 行受影响)
*/
go
drop table tb
没有规律,只是根据要求做了一下,改变数据,可能就不是你要的样子了.
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([Col1] int,[Col2] nvarchar(3),[Col3] nvarchar(7),[Col4] nvarchar(9),[Col5] nvarchar(9))
Insert #T
select 1,N'lxd',N'punggol',N'singapore',null union all
select 2,N'lxd',N'punggol',null,N'singapore' union all
select 2,N'shy',N'beijing',null,N'my love' union all
select 2,N'shy',N'beijing',N'china',null
Go
Select
[Col1]=ROW_NUMBER()over(order by min([Col1])),
[Col2],[Col3],[Col4]=MAX([Col4]),[Col5]=MAX([Col5])
from #T group by [Col2],[Col3]/*
Col1 Col2 Col3 Col4 Col5
1 lxd punggol singapore singapore
2 shy beijing china my love
*/