[MSSQL] 字串相加欄位合併-FOR XML PATH

sql 欄位相加欄位合併

圓頭人 2018-01-30 11:22:20 ‧ 57246 瀏覽

分享至

這些國家,哪些天有比賽?

準備資料

drop table #UEFO_Country
CREATE TABLE #UEFO_Country
(
	name varchar(30)
	,rundate int
)

insert into #UEFO_Country
values	('German',20180101)
		,('Spain',20180102)
		,('France',20180103)
		,('France',20180105)
		,('Italy',20180104)		
		,('Italy',20180106)
		,('Italy',20180107)


select name  from #UEFO_Country

字串相加方法1

declare   @String varchar(8000)=''
select @String=@String +'.'+name  from #UEFO_Country
select @String

字串相加方法2-FOR XML PATH

select name,	
(
	SELECT cast(rundate AS NVARCHAR ) + '.' 
    from #UEFO_Country  	
	where name=t0.name	--把name一樣的加起來
	FOR XML PATH('')
) as DateList
from #UEFO_Country t0

過濾重複資料-使用 DISTINCT 語法

select DISTINCT  name,
datelist=
(
	SELECT cast(rundate AS NVARCHAR ) + '.' 
    from #UEFO_Country  	
	where name=t0.name	--把name一樣的加起來
	FOR XML PATH('')
)
from #UEFO_Country t0

過濾重複資料-使用GROUP 語法

select name,datelist  from 
(
	select name,
	datelist=
	(
		SELECT cast(rundate AS NVARCHAR ) + '.' from #UEFO_Country  	
		where name=t0.name	--把name一樣的加起來
		FOR XML PATH('')
	)
	from #UEFO_Country t0
) a
group by name,datelist 
order by datelist