USE [xb_new]
GO
/****** 对象:  Table [dbo].[Question]    脚本日期: 06/13/2010 21:17:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Question](
[id] [int] IDENTITY(1,1) NOT NULL,
[iss_id] [int] NULL,
[answer] [nvarchar](50) NULL,
[question] [nvarchar](150) NULL,
CONSTRAINT [PK_Question_] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH  (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,  ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

表中数据如下：

表二：

USE [xb_new]
GO
/****** 对象:  Table [dbo].[Qusetion_Answer]    脚本日期: 06/13/2010 21:18:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Qusetion_Answer](
[id] [int] IDENTITY(1,1) NOT NULL,
[qid] [int] NOT NULL,
[qtext] [nvarchar](50) NOT NULL,
[qvalue] [nvarchar](2) NULL
) ON [PRIMARY]

表中数据如下

现在需求是：把一个题目的四个选项合并到一起，并为了在网页上换行，显示题目，显示答案。
其中难道是行列转换，以前在orcal中都是使用decode来实现的，现在郁闷的sql server中竟然不支持这个函数，只能我在oracle中比较讨厌的case（主要要多写很多when、then、end我不喜欢，而且还容易忘记格 式）来实现。实现sql脚本如下：

SELECT a,q,aa+'<br>'+bb+'<br>'+cc+'<br>'+dd result FROM
(SELECT DISTINCT answer a,Question.Question q,
aa=MAX(CASE qvalue WHEN 'A' THEN '(A)'+qtext END),
bb=max(CASE qvalue WHEN 'B' THEN '(B)'+qtext END),
cc=max(CASE qvalue WHEN 'C' THEN '(C)'+qtext END),
dd=max(CASE qvalue WHEN 'D' THEN '(D)'+qtext END)
FROM dbo.Question,dbo.Qusetion_Answer
WHERE qid=dbo.Question.id GROUP BY dbo.Question.answer,Question.Question) temp;

结果如下：

ALTER PROC [dbo].[add_data_yes]
@tablename varchar(30),
@id INT,
@jh INT
AS
declare @sql nvarchar(4000)
SET @sql=N'update S_del set jh='+CAST(@jh AS VARCHAR(10))+N' where id='+CAST(@id AS VARCHAR(10))+N';INSERT INTO <a href="mailto:%27+@tablename+N%27" target="_blank">'+@tablename+N'</a>
( tm ,
jh ,
ajh ,
gjz ,
sjms ,
fsdd ,
fsrq ,
zyrw ,
psz ,
psrq ,
tgz ,
zrz ,
sc ,
ly ,
cjh ,
srz ,
flh ,
dph ,
bz ,
xpdx ,
xpgs ,
xpxs ,
scrid ,
scr ,
scsj ,
scbm ,
shr ,
shsj ,
ecbz ,
gdr ,
gdsj ,
xgsj ,
flag ,
ysurl ,
ysfile ,
xgurl ,
xgfile ,
xwurl ,
xwnr ,
textall
)
SELECT
tm ,
jh ,
ajh ,
gjz ,
sjms ,
fsdd ,
fsrq ,
zyrw ,
psz ,
psrq ,
tgz ,
zrz ,
sc ,
ly ,
cjh ,
srz ,
flh ,
dph ,
bz ,
xpdx ,
xpgs ,
xpxs ,
scrid ,
scr ,
scsj ,
scbm ,
shr ,
shsj ,
ecbz ,
gdr ,
gdsj ,
xgsj ,
flag ,
ysurl ,
ysfile ,
xgurl ,
xgfile ,
xwurl ,
xwnr ,
textall
FROM S_del WHERE id='+CAST(@id AS varchar(10))+N';delete from S_del  where id='+CAST(@id AS varchar(10))+N';insert into S_bm_all select *  from <a href="mailto:%27+@tablename+N%27" target="_blank">'+@tablename+N'</a> where id=(select max(id) from <a href="mailto:%27+@tablename+N%27%29%27" target="_blank">'+@tablename+N')'</a>
EXEC(@sql)

好了，以前是因为自己对存储过程没有入门，写了几个放在上面了，现在感觉对存储过程有了一点状态，不再放存储过程山来了（我感觉特别有意义的除外）

ALTER PROC [dbo].[guidang]
@tablename varchar(30),
@id int ,
@tm varchar(500),
@jh int,
@t DATETIME ,
@ajh INT,
@gdr VARCHAR(10)
AS
DECLARE @sql VARCHAR(2000) SET @sql='update <a href="mailto:%27+@tablename+%27" target="_blank">'
+@tablename+'</a> set flag='''+CAST(3 AS  VARCHAR(1))+''',gdr='''+@gdr+''',
gdsj='''+CAST(@t AS VARCHAR(25))+''',jh='+CAST(@jh AS varchar(10))+',
ajh='+CAST(@ajh AS  VARCHAR(10))+',tm='''+@tm+''',textall=tm+'+
'''|'''+'+isnull(gjz,'''')+'+'''|'''+'+isnull(gjz,'''')+'+'''|'''
+'+isnull(fsdd,'''')+'+'''|'''+
'+isnull(zyrw,'''')+'+'''|'''+'+isnull(psz,'''')+'+'''|'''
+'+isnull(tgz,'''')+'
+'''|'''+'+isnull(zrz,'''')+'+'''|'''+'+isnull(srz,'''')+'
+'''|'''+'+isnull(bz,'''')+'+'''|'''+'+isnull(scr,'''')+'+'''|'''+
'+isnull(shr,'''')+'+'''|'''+'
+isnull(gdr,'''')where id='+CAST(@id AS VARCHAR(10))+';insert into S_bm_all
select *  from <a href="mailto:%27+@tablename+%27" target="_blank">'
+@tablename+'</a> where id='+CAST(@id AS VARCHAR(10))
EXEC(@sql)

在这个编写过程中，如果汇总项中，有一项为null，就会得到textall为null，这个困惑了我很久，最后发现了问题在这，用isnull函数解决问题，还有一个细节问题，“‘”有表示转义的意思