原数据结构
需要的结果
SQL语句
1
2
if
exists
(
select
*
from
sysobjects
where
id
=
object_id
(
'
Page
'
)
and
type
=
'
u
'
)
3
drop
table
Page
4![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
create
table
Page
6
(
7
PageID
int
primary
key
,
8
PageText
nvarchar
(
4000
)
9
)
10![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
11
declare
@bookid
nvarchar
(
10
)
12
declare
@sql
nvarchar
(
4000
)
13![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
14![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
15
--
定义游标
16
DECLARE
Book_Cursor
CURSOR
FOR
SELECT
BookID
From
BookInfo
order
by
bookid
17
open
Book_Cursor
18
FETCH
NEXT
FROM
Book_Cursor
Into
@bookid
19
WHILE
@@FETCH_STATUS
=
0
20
BEGIN
21
--
print @bookid
22
23
set
@sql
=
'
24
insert into Page
25
SELECT *
26
FROM(
27
SELECT DISTINCT pageid FROM Areainfo_
'
+
@bookid
+
'
28
)AA
29
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 pageid
45
FOR XML AUTO
46
),
''
<t Text="
''
,
''''
),
''
"/>
''
,
''''
), 1, 1,
''''
)
47
)N
'
48
--
print @sql
49
exec
(
@sql
)
50
FETCH
NEXT
FROM
Book_Cursor
Into
@bookid
51
end
52
close
Book_Cursor
53
deallocate
Book_Cursor
54![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
55
select
*
from
page
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
13
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
14
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
15
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
16
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
17
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
18
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
19
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
20
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
21
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
22
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
23
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
24
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
25
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
26
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
27
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
28
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
29
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
30
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
31
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
32
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
33
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
34
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
35
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
36
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
37
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
38
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
39
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
40
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
41
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
42
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
43
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
44
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
45
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
46
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
47
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
48
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
49
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
50
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
51
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
52
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
53
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
54
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
55
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)