SQL Server2005数据项的分拆与合并

核心提示：示例：SET NOcount ONcreate TABLE ##Table([keyCol] varchar(3), [NewValues] varchar(max))--原数据select[title_id],[title]FROM [pubs].[dbo].[titles]where [type] like &rs

示例：

SET NOcount ON

create TABLE ##Table([keyCol] varchar(3), [NewValues] varchar(max))

--原数据

select
[title_id]
,[title]
FROM [pubs].[dbo].[titles]
where [type] like ’p%’

--以title_id的前两个字符为参考键值，合并title到一个临时表中

insert INTO ##Table
execute [ChineseHoliday].[Helper].[joinValue]
@TableName = ’[pubs].[dbo].[titles]’
,@KeyColName = ’LEFT([title_id], 2)’
,@joinColName = ’’’《’’+[title] + ’’》’’’
,@Quote = ’,’
,@where = ’[type] like ’’p%’’’

--显示

select * FROM ##Table

--对临时表NewValues的值进行分拆

execute [ChineseHoliday].[Helper].[SpliteValues]
@TableName = ’##Table’
,@KeyColName = ’[keyCol]’
,@SpliteColName = ’[NewValues]’
,@Quote = ’,’

--删除临时表

drop TABLE ##Table

结果：

title_id title

-------- -------------------------------------------------------------------------------

PC1035 But Is It User Friendly?

PC8888 Secrets of Silicon Valley

PC9999 Net Etiquette

PS1372 Computer Phobic AND Non-Phobic Individuals: Behavior Variations

PS2091 Is Anger the Enemy?

PS2106 Life Without Fear

PS3333 Prolonged Data Deprivation: Four Case Studies

PS7777 Emotional Security: A New Algorithm

keyCol NewValues

------ ------------------------------------------