mssql 字符串的拼接(join)与切分(split)
经常有高手使用select number from master..spt_values where type = 'p'，这是很妙的方法；但这样只有2048个数字，而且语句太长，不够方便。
总之，一个数字辅助表（10万还是100万根据个人需要而定），你值得拥有。
2. 日历表
有用指数：★★★☆☆
《sql编程风格》一书建议一个企业的数据库教程应该创建一个日历表：
sql code
create table calendar(
date datetime not null primary key clustered,
weeknum int not null,
weekday int not null,
weekday_desc nchar(3) not null,
is_workday bit not null,
is_weekend bit not null
)
go
with cte1 as(
select
date = dateadd(day,n,'19991231')
from nums
where n <= datediff(day,'19991231','20201231')),
cte2 as(
select
date,
weeknum = datepart(week,date),
weekday = (datepart(weekday,date) + @@datefirst - 1) % 7,
weekday_desc = datename(weekday,date)
from cte1)
--insert into calendar
select
date,
weeknum,
weekday,
weekday_desc,
is_workday = case when weekday in (0,6) then 0 else 1 end,
is_weekend = case when weekday in (0,6) then 1 else 0 end
from cte2
这个表可以很容易根据第1条的数字辅助表生成出来。如果经常需要进行日期处理的话，或许会需要这个表。
还可以在这个表中包含一些企业关心的特殊日期，比如开盘日休市日（股票行业）、特殊纪念日和节日、重要员工的生日，等等。这些日期往往是很难计算的，比如中国的法定节假日（农历问题）。
3. 字符串的拼接(join)与切分(split)
有用指数：★★★★★
这个问题非常常见！开发中经常需要把一组值以逗号分隔拼接在一个字符串，或是反过来把一个逗号分隔的字符串切分成一组值。
用ss2005对xml的支持可以非常方便地实现这个功能。
单变量的拼接与切分：
sql code
--将一组查询结果按指定分隔符拼接到一个变量中
declare @datebases varchar(max)
set @datebases = stuff((
select ','+name
from sys.databases
order by name
for xml path('')),1,1,'')
select @datebases
--将传入的一个参数按指定分隔符切分到一个表中
declare @sourceids varchar(max)
set @sourceids = 'a,bcd,123,+-*/=,x&y,<key>'
select v = x.n.value('.','varchar(10)')
from (
select valuesxml = cast('<root>' +
replace((select v = @sourceids for xml path('')),',','</v><v>') +
'</root>' as xml)
) t
cross apply t.valuesxml.nodes('/root/v') x(n)
批量的拼接与切分：
sql code
--测试数据：
create table #tojoin(
tablename varchar(20) not null,
columnname varchar(20) not null,
primary key clustered(tablename,columnname))
go
create table #tosplit(
tablename varchar(20) not null primary key clustered,
columnnames varchar(max) not null)
go
insert into #tojoin values('tblemployee','employeecode')
insert into #tojoin values('tblemployee','employeename')
insert into #tojoin values('tblemployee','hiredate')
insert into #tojoin values('tblemployee','jobcode')
insert into #tojoin values('tblemployee','reporttocode')
insert into #tojoin values('tbljob','jobcode')
insert into #tojoin values('tbljob','jobtitle')
insert into #tojoin values('tbljob','joblevel')
insert into #tojoin values('tbljob','departmentcode')
insert into #tojoin values('tbldepartment','departmentcode')
insert into #tojoin values('tbldepartment','departmentname')
go
insert into #tosplit values('tbldepartment','departmentcode,departmentname')
insert into #tosplit values('tblemployee','employeecode,employeename,hiredate,jobcode,reporttocode')
insert into #tosplit values('tbljob','departmentcode,jobcode,joblevel,jobtitle')
go--拼接(join)，sql server 2005的for xml扩展可以将一个列表转成一个字串：
select
t.tablename,
columnnames = stuff(
(select ',' + c.columnname
from #tojoin c
where c.tablename = t.tablename
for xml path('')),
1,1,'')
from #tojoin t
group by t.tablename--切分(split)，使用sql server 2005对xquery的支持：
select
t.tablename,
columnname = c.columnname.value('.','varchar(20)')
from (
select
tablename,
columnnamesxml = cast('<root>' + replace((select columnname = columnnames for xml path('')),',','</columnname><columnname>') + '</root>' as xml)
from #tosplit
) t
cross apply t.columnnamesxml.nodes('/root/columnname') c(columnname)
需要注意的是，倘若分隔符为";"或者字符串值中包含xml特殊字符（比如&、<、>等等），以上方法可能会无法处理。