多条记录合并成一条记录
原数据结构
需要的结果
SQL语句
1
2if exists(select * from sysobjects where id = object_id('Page') and type='u' )
3 drop table Page
4
5create table Page
6(
7 PageID int primary key,
8 PageText nvarchar(4000)
9)
10
11declare @bookid nvarchar(10)
12declare @sql nvarchar(4000)
13
14
15--定义游标
16DECLARE Book_Cursor CURSOR FOR SELECT BookID From BookInfo order by bookid
17open Book_Cursor
18FETCH NEXT FROM Book_Cursor Into @bookid
19WHILE @@FETCH_STATUS = 0
20BEGIN
21--print @bookid
22
23set @sql='
24insert into Page
25SELECT *
26FROM(
27 SELECT DISTINCT pageid FROM Areainfo_'+@bookid+'
28)AA
29OUTER APPLY(
30 SELECT
31 [values]= STUFF(REPLACE(REPLACE(
32 (
33SELECT [Text] =
34CASE
35WHEN [TextReplace] IS NOT NULL THEN [TextReplace]
36WHEN [TextReceive] IS NOT NULL THEN [TextReceive]
37WHEN [TextProfessor] IS NOT NULL THEN [TextProfessor]
38WHEN [TextLandscape] IS NOT NULL THEN [TextLandscape]
39WHEN [TextPortrait] IS NOT NULL THEN [TextPortrait]
40WHEN [TextRecension] IS NOT NULL THEN [TextRecension]
41WHEN [TextSecond] IS NOT NULL THEN [TextSecond]
42ELSE [TextFirst]
43END
44FROM textinfo_'+@bookid+' as t left join Areainfo_'+@bookid+' as a ON a.areaid=t.areaid WHERE pageid = AA.pageid order by pageid
45 FOR XML AUTO
46 ),''<t Text="'', ''''), ''"/>'', ''''), 1, 1,'''')
47)N'
48--print @sql
49exec(@sql)
50FETCH NEXT FROM Book_Cursor Into @bookid
51end
52close Book_Cursor
53deallocate Book_Cursor
54
55select * from page
2
if exists(select * from sysobjects where id = object_id('Page') and type='u' )3
drop table Page4
5
create table Page6
(7
PageID int primary key,8
PageText nvarchar(4000)9
)10
11
declare @bookid nvarchar(10)12
declare @sql nvarchar(4000)13
14
15
--定义游标16
DECLARE Book_Cursor CURSOR FOR SELECT BookID From BookInfo order by bookid17
open Book_Cursor18
FETCH NEXT FROM Book_Cursor Into @bookid19
WHILE @@FETCH_STATUS = 020
BEGIN21
--print @bookid22
23
set @sql='24
insert into Page25
SELECT *26
FROM(27
SELECT DISTINCT pageid FROM Areainfo_'+@bookid+'28
)AA29
OUTER APPLY(30
SELECT 31
[values]= STUFF(REPLACE(REPLACE(32
(33
SELECT [Text] = 34
CASE 35
WHEN [TextReplace] IS NOT NULL THEN [TextReplace]36
WHEN [TextReceive] IS NOT NULL THEN [TextReceive]37
WHEN [TextProfessor] IS NOT NULL THEN [TextProfessor]38
WHEN [TextLandscape] IS NOT NULL THEN [TextLandscape]39
WHEN [TextPortrait] IS NOT NULL THEN [TextPortrait]40
WHEN [TextRecension] IS NOT NULL THEN [TextRecension]41
WHEN [TextSecond] IS NOT NULL THEN [TextSecond]42
ELSE [TextFirst]43
END 44
FROM textinfo_'+@bookid+' as t left join Areainfo_'+@bookid+' as a ON a.areaid=t.areaid WHERE pageid = AA.pageid order by pageid45
FOR XML AUTO46
),''<t Text="'', ''''), ''"/>'', ''''), 1, 1,'''')47
)N'48
--print @sql49
exec(@sql)50
FETCH NEXT FROM Book_Cursor Into @bookid51
end52
close Book_Cursor53
deallocate Book_Cursor54
55
select * from page
浙公网安备 33010602011771号