讲解SQL Server2005数据项的分拆与合并
作者:Victor 发布时间:2009-01-04 14:40:00
SQL Server2005数据项的分拆与合并:
参考示例如下:
-- =============================================
-- Author: LzmTW
-- Create date: 20080102
-- Description: 连接子字符串
-- @TableName: 数据所在的表的名称
-- @KeyColName: 连接子字符串所依据的键值所在的列
-- @JoinColName: 包含要连接的子字符串所在的列
-- @Quote: 分隔子字符串
-- @Where: 选择条件,不包含Where
-- =============================================
CREATE PROCEDURE [Helper].[JoinValue]
@TableName nvarchar(100)
,@KeyColName nvarchar(20)
,@JoinColName nvarchar(20)
,@Quote nvarchar(10) = N','
,@Where nvarchar(max) = NULL
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@SQL nvarchar(max)
IF @Where IS NULL
SET @SQL = N'
SELECT *
FROM
(
SELECT DISTINCT KeyCol = @KeyColName
FROM @TableName
)a
'
ELSE
SET @SQL = N'
SELECT *
FROM
(
SELECT DISTINCT KeyCol = @KeyColName
FROM @TableName
WHERE @Where
)a
'
SET @SQL = @SQL + N'
OUTER APPLY (
SELECT NewValues =
STUFF(
REPLACE(
REPLACE(
REPLACE(
(
SELECT JoinCol = @JoinColName
FROM @TableName b
WHERE @KeyColName = a.KeyCol
FOR XML RAW
)
, N'''', N'''')
, N'', N''"/>'', N'''')
, 1, LEN(N''@Quote''), N'''')
) c'
SET @SQL = REPLACE(@SQL, N'@TableName', @TableName)
SET @SQL = REPLACE(@SQL, N'@KeyColName', @KeyColName)
SET @SQL = REPLACE(@SQL, N'@JoinColName', @JoinColName)
SET @SQL = REPLACE(@SQL, N'@Quote', @Quote)
IF NOT @Where IS NULL
SET @SQL = REPLACE(@SQL, N'@Where', @Where)
--PRINT @SQL
EXEC sp_executesql @SQL
END
GO
-- =============================================
-- Author: LzmTW
-- Create date: 20080102
-- Description: 分拆字符串
-- @TableName: 数据所在的表的名称
-- @KeyColName: 分拆为子字符串所依据的键值所在的列
-- @SpliteColName: 包含要分拆的字符串所在的列
-- @Quote: 分隔子字符串
-- @Where: 选择条件,不包含Where
-- =============================================
CREATE PROCEDURE [Helper].[SpliteValues]
@TableName nvarchar(100)
,@KeyColName nvarchar(20)
,@SpliteColName nvarchar(20)
,@Quote nvarchar(10) = N','
,@Where nvarchar(max) = NULL
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@SQL nvarchar(max)
IF @Where IS NULL
SET @SQL = N'
SELECT
KeyCol, NewValue
FROM
(
SELECT
KeyCol = @KeyColName
,SpliteCol = CONVERT(xml, N'''' + REPLACE(@SpliteColName, N''@Quote'', N'''') + N'''')
FROM @TableName
) a
'
ELSE
SET @SQL = N'
SELECT
KeyCol, NewValue
FROM
(
SELECT
KeyCol = @KeyColName
,SpliteCol = CONVERT(xml, N'''' + REPLACE(@SpliteColName, N''@Quote'', N'''') + N'''')
FROM @TableName
WHERE @Where
) a
'
SET @SQL = @SQL + N'
OUTER APPLY
(
SELECT NewValue = N.v.value(N''.'', ''nvarchar(max)'')
FROM SpliteCol.nodes(N''/root/v'') N(v)
) b'
SET @SQL = REPLACE(@SQL, N'@TableName', @TableName)
SET @SQL = REPLACE(@SQL, N'@KeyColName', @KeyColName)
SET @SQL = REPLACE(@SQL, N'@SpliteColName', @SpliteColName)
SET @SQL = REPLACE(@SQL, N'@Quote', @Quote)
IF NOT @Where IS NULL
SET @SQL = REPLACE(@SQL, N'@Where', @Where)
EXEC sp_executesql @Sql
END
示例:
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
------ ------------------------------------------
PC 《But Is It User Friendly?》,《Secrets of Silicon Valley》,《Net Etiquette》
PS 《Computer Phobic AND Non-Phobic Individuals: Behavior Variations》,《Is Anger the Enemy?》,《Life Without Fear》,《Prolonged Data Deprivation: Four Case Studies》,《Emotional Security: A New Algorithm》
KeyCol NewValue
------ ------------------------------------------
PC 《But Is It User Friendly?》
PC 《Secrets of Silicon Valley》
PC 《Net Etiquette》
PS 《Computer Phobic AND Non-Phobic Individuals: Behavior Variations》
PS 《Is Anger the Enemy?》
PS 《Life Without Fear》
PS 《Prolonged Data Deprivation: Four Case Studies》
PS 《Emotional Security: A New Algorithm》
继续:字符串的分拆
-- =============================================
-- Author: LzmTW
-- Create date: 20080108
-- Description: 拆分字符串
-- =============================================
CREATE FUNCTION [Func].[Splite]
(
@Input nvarchar(max)
,@Quote nvarchar(max)
)
RETURNS
@Table TABLE
(
[ID] int identity(1,1) PRIMARY KEY
,[Value] nvarchar(max)
)
AS
BEGIN
INSERT @Table
SELECT
[Value] = NewValue
FROM
(
SELECT
SpliteCol = CONVERT(
xml
,N'' + REPLACE(
@Input
,@Quote
,N'') + N'')
) a
OUTER APPLY
(
SELECT NewValue = N.v.value(N'.', 'nvarchar(max)')
FROM SpliteCol.nodes(N'/root/v') N(v)
) b
RETURN
END
示例:
定义新行,
CREATE FUNCTION [Const].[NewLine]
(
)
RETURNS nchar(2)
AS
BEGIN
DECLARE @Result nchar(2)
SELECT @Result = char(13) + char(10)
RETURN @Result
END
DECLARE
@Input nvarchar(max)
,@Quote nvarchar(max)
SET @Input = N'90
10
20
30
40
50
60'
SET @Quote = [Const].NewLine()
SELECT * FROM [Func].[Splite] (@Input, @Quote)
结果
ID Value
----------- ------
1 90
2 10
3 20
4 30
5 40
6 50
7 60
(7 行受影响)
猜你喜欢
- 说到网络产品,离不开的话题就是用户,就像传统行业的消费者。人是复杂的,网民的用户行为更加复杂,用户和用户是不一样的,或者说,每个用户都不一样
- 下面的代码是日期函数的一些简单运用,应该不用解释,生成当月的日历,当然你可以根据实际情况进行扩充!效果图:<%@LANGUAGE=&q
- 背景在吉日嘎拉的软件编程走火入魔之:数据库事务处理入门(适合初学者阅读)文章中关于MS SQL Server和Oracle对数据库事务处理的
- 一、IE透明度问题在IE的高度超过某一阀值时,会产生透明度不时失效的问题,这现象比较奇怪,(会有的时候全黑,有的时候全白)你有可能无法复现。
- 忽然想起一个CSS的特性,写一段代码玩玩:<style type="text/css">body {font
- 使用Northwind 数据库首先查询Employees表查询结果:city列里面只有5个城市使用ROW_NUMBER() OVER(PAR
- 最近看到一个词叫css reset。什么叫做css reset呢?我理解为重置css,也就是重置默认样式。我在HTML下的默认样式中讲到,一
- 英文原文:http://www.456bereastreet.com/archive/200601/css_3_selectors_expl
- 不过有一个简单的执行cmd命令方法: SQL> host net user User accounts for \\PC-ATQHJ4
- 1.1.1 摘要 如果说要对数据库进行优化,我们主要可以通过以下五种方法,对数据库系统进行优化。 1. 计算机硬件调优 2. 应用程序调优
- 先让我们看一个例子,了解什么是模式化窗口。以下是QQ秀商城在非登录时提示登录的一种状态。当我在非登录状态,通过保存形象的方式买一件衣服时,弹
- asp之家注:长文章分页算是asp编程中一个比较经典单位问题,怎么分页,什么时候分页.方法挺多,有的是人为的加入分页标志,有的是程序自动加分
- 通常情况下,即使MyISAM表格式非常可靠(SQL语句对表做的所有改变在语句返回之前被写下),如果下列任何事件发生,你依然可以获得损坏的表:
- 一个asp读取数据库中数据到数组的类,仅供参考!DbPath = "test.mdb"’数据库位置&
- 如何做一个树状展开视图来显示自己的记录结构?在SQL中,如何做一个可收起和展开树状结构图?就是资源管理器左栏的那种效果。这要用到Data s
- 我很想自己写一个这样的程序,不过wordpress是php的,我blog是ASP的,只好用ASP写代码了。经过一番折腾测试,终于让我的梦想变
- 一、出错情况 有些时候当你重启了数据库服务,会发现有些数据库变成了正在恢复、置疑、可疑等情况,这个时候DBA就会很紧张了,下面是一些在实践中
- Request.ServerVariables("Url") 返回服务器地址Request.ServerVariable
- Asp开发 联通CDMA以下是在开发wap中的随笔,其中一些对于“老鸟”来说,谈不上什么,希望对初学者有所帮助,大家有什么小技巧,欢迎顶上来
- 1、先介绍如何用PHP连上数据库(数据库用户名“root”,密码“sun”,有库“myguestbook”) 图62、PHP和My