--看看老大的
CREATE TABLE A(编号 int, 内容 ntext)
INSERT A
select 1,'xxxx' union all
select 1,'yyyyy' union all
select 2,'aaa' union all
select 4,'ccccc' union all
select 4,'ddddd' union all
select 4,'eeeee'
GO-- 处理
SELECT pkid = IDENTITY(int,1,1), flag = 0, 编号, 内容
INTO #
FROM ADECLARE @pkid int, @编号 int, @编号1 int,@p1 binary(16), @p2 binary(16),@SP nvarchar(10)SET @SP = '分隔符'DECLARE tb CURSOR LOCAL FOR SELECT pkid, 编号, TEXTPTR(内容) FROM # ORDER BY 编号, pkid
OPEN tb FETCH tb INTO @pkid, @编号, @p2WHILE @@FETCH_STATUS = 0
BEGIN
IF @编号1 = @编号
BEGIN
UPDATETEXT #.内容 @p1 NULL 0 @SP
UPDATETEXT #.内容 @p1 NULL 0 #.内容 @p2
END
ELSE
BEGIN
UPDATE # SET flag = 1, @p1 = @p2, @编号1 = @编号
WHERE pkid = @pkid
END
FETCH tb INTO @pkid, @编号, @p2
ENDCLOSE tb
DEALLOCATE tbSELECT 编号, 内容 FROM # WHERE flag = 1
GODROP TABLE #, A--结果:
/*
编号 内容
1 xxxx分隔符yyyyy
2 aaa
4 ccccc分隔符ddddd分隔符eeeee
*/
CREATE TABLE A(编号 int, 内容 ntext)
INSERT A
select 1,'xxxx' union all
select 1,'yyyyy' union all
select 2,'aaa' union all
select 4,'ccccc' union all
select 4,'ddddd' union all
select 4,'eeeee'
GO-- 处理
SELECT pkid = IDENTITY(int,1,1), flag = 0, 编号, 内容
INTO #
FROM ADECLARE @pkid int, @编号 int, @编号1 int,@p1 binary(16), @p2 binary(16),@SP nvarchar(10)SET @SP = '分隔符'DECLARE tb CURSOR LOCAL FOR SELECT pkid, 编号, TEXTPTR(内容) FROM # ORDER BY 编号, pkid
OPEN tb FETCH tb INTO @pkid, @编号, @p2WHILE @@FETCH_STATUS = 0
BEGIN
IF @编号1 = @编号
BEGIN
UPDATETEXT #.内容 @p1 NULL 0 @SP
UPDATETEXT #.内容 @p1 NULL 0 #.内容 @p2
END
ELSE
BEGIN
UPDATE # SET flag = 1, @p1 = @p2, @编号1 = @编号
WHERE pkid = @pkid
END
FETCH tb INTO @pkid, @编号, @p2
ENDCLOSE tb
DEALLOCATE tbSELECT 编号, 内容 FROM # WHERE flag = 1
GODROP TABLE #, A--结果:
/*
编号 内容
1 xxxx分隔符yyyyy
2 aaa
4 ccccc分隔符ddddd分隔符eeeee
*/
CREATE TABLE A(编号 nvarchar(16), 内容 image)
INSERT A
select 'A', 0x111111 union all
select 'A', 0x222222 union all
select 'A', 0x333333 union all
select 'A', 0x444444 union all
select 'B', 0x111111 union all
select 'B', 0x222222
GO-- 处理
SELECT pkid = IDENTITY(int,1,1), flag = 0, 编号, 内容
INTO #
FROM ADECLARE @pkid int, @编号 nvarchar(16), @编号1 nvarchar(16),@p1 binary(16), @p2 binary(16),@SP nvarchar(10)SET @SP = ''DECLARE tb CURSOR LOCAL FOR SELECT pkid, 编号, TEXTPTR(内容) FROM # ORDER BY 编号, pkid
OPEN tb FETCH tb INTO @pkid, @编号, @p2WHILE @@FETCH_STATUS = 0
BEGIN
IF @编号1 = @编号
BEGIN
UPDATETEXT #.内容 @p1 NULL 0 @SP
UPDATETEXT #.内容 @p1 NULL 0 #.内容 @p2
END
ELSE
BEGIN
UPDATE # SET flag = 1, @p1 = @p2, @编号1 = @编号
WHERE pkid = @pkid
END
FETCH tb INTO @pkid, @编号, @p2
ENDCLOSE tb
DEALLOCATE tbSELECT 编号, 内容 FROM # WHERE flag = 1
GODROP TABLE #, A--结果:
/*
编号 内容
-------------------------------------------------
A 0x111111222222333333444444
B 0x111111222222
*/