游标、临时表、嵌套游标使用一列

表结构：

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Log]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[Log]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Users]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[Users]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[options]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[options]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[questionAndAnswer]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[questionAndAnswer]

CREATE TABLE [dbo].[Log] (

[Id] [int] IDENTITY (1, 1) NOT NULL ,

[Typed] [varchar] (256) COLLATE Chinese_PRC_CI_AS NULL ,

[CreateDate] [datetime] NULL

) ON [PRIMARY]

CREATE TABLE [dbo].[Users] (

[id] [int] IDENTITY (1, 1) NOT NULL ,

[firstName] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,

[lastName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,

[address1] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,

[address2] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,

[city] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,

[state] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,

[zip] [char] (5) COLLATE Chinese_PRC_CI_AS NULL ,

[birthday] [datetime] NULL ,

[phone] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,

[email] [varchar] (256) COLLATE Chinese_PRC_CI_AS NULL ,

[CreatedDate] [datetime] NULL

) ON [PRIMARY]

CREATE TABLE [dbo].[options] (

[questionId] [int] NOT NULL ,

[optionId] [int] NOT NULL ,

[optionBody] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL

) ON [PRIMARY]

CREATE TABLE [dbo].[questionAndAnswer] (

[id] [int] IDENTITY (1, 1) NOT NULL ,

[userId] [int] NULL ,

[questionId] [int] NOT NULL ,

[optionId] [int] NOT NULL ,

[answer] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL

) ON [PRIMARY]

需要插入数据的请下载sql文件到查询分析器中执行一下.
存储过程：

CREATE PROCEDURE GetReport

@beginDate datetime,

@endDate datetime

SET NOCOUNT ON

---如果存在同名临时表，则将它删除

IF EXISTS(SELECT NAME FROM tempdb.dbo.sysobjects WHERE NAME LIKE '#TempTable' AND type='U')

DROP TABLE #TempTable

--创建临时表

CREATE TABLE #TempTable

(

[id] INT NOT NULL primary key,

[firstName] VARCHAR(30),

[lastName] VARCHAR(50) ,

[address1] VARCHAR(200),

[address2] VARCHAR(200),

[city] VARCHAR(50),

[state] VARCHAR(20),

[zip] CHAR(5),

[birthday] DATETIME,

[phone] VARCHAR(20),

[email] VARCHAR(256),

[CreatedDate] DATETIME,

op1 VARCHAR(1000),

op2 VARCHAR(1000),

op3 VARCHAR(1000),

op4 VARCHAR(1000),

op5 VARCHAR(1000),

op6 VARCHAR(100),

op7 VARCHAR(500),

op8 VARCHAR(1000),

op9 VARCHAR(100)

)

--定义用于取出游标记录值的变量

DECLARE @id INT

DECLARE @firstName VARCHAR(30)

DECLARE @lastName VARCHAR(50)

DECLARE @address1 VARCHAR(200)

DECLARE @address2 VARCHAR(200)

DECLARE @city VARCHAR(50)

DECLARE @state VARCHAR(20)

DECLARE @zip CHAR(5)

DECLARE @birthday DATETIME

DECLARE @phone VARCHAR(20)

DECLARE @email VARCHAR(256)

DECLARE @CreatedDate DATETIME

--使用游标取出数据，将根据它填充临时表中的用户信息

DECLARE userCursor CURSOR FOR

SELECT u.id,u.firstName,u.lastName,u.address1,u.address2,u.city,u.state,u.zip,u.birthday,u.phone,u.email,u.CreatedDate FROM USERS u WHERE @beginDate<u.CreatedDate and u.CreatedDate< @endDate

--打开游标

OPEN userCursor

--指向第一行

FETCH NEXT FROM userCursor INTO @id,@firstName,@lastName,@address1,@address2,@city,@state,@zip,@birthday,@phone,@email,@CreatedDate

--查看游标状态是否可读

WHILE @@FETCH_STATUS = 0

BEGIN

--往临时表中填入报表每条记录的前半部分信息(用户信息)

INSERT INTO #TempTable([id],firstName,lastName,address1,address2,city,state,zip,birthday,phone,email,CreatedDate)VALUES(@id,@firstName,@lastName,@address1,@address2,@city,@state,@zip,@birthday,@phone,@email,@CreatedDate)

--用于记录每个问题的所有option，每个变量记录单个问题的所有option，

DECLARE @option1 VARCHAR(1000)

DECLARE @option2 VARCHAR(1000)

DECLARE @option3 VARCHAR(1000)

DECLARE @option4 VARCHAR(1000)

DECLARE @option5 VARCHAR(1000)

DECLARE @option6 VARCHAR(100)

DECLARE @option7 VARCHAR(500)

DECLARE @option8 VARCHAR(1000)

DECLARE @option9 VARCHAR(100)

--为每个变量符初始值，否则相加时会返回null。因为null+'XXX'返回null

SELECT @option1=''

SELECT @option2=''

SELECT @option3=''

SELECT @option4=''

SELECT @option5=''

SELECT @option6=''

SELECT @option7=''

SELECT @option8=''

SELECT @option9=''

--定义变量，用于暂存每条option记录

DECLARE @USERID INT

DECLARE @OPTIONBODY VARCHAR(200)

DECLARE @QUESTIONID INT

DECLARE @OPTIONID INT

--获取一个user的所有options

DECLARE optionCursor CURSOR FOR

SELECT q.userid,

(CASE q.answer

WHEN 'checked' THEN o.optionBody

ELSE o.optionBody + q.answer

END) AS optionBody,--此处合并填写的信息，如other:XXXX

q.questionId,

q.optionId

FROM options o,questionAndAnswer q

WHERE q.questionId=o.questionId and q.optionId=o.optionId and q.userId=@id

--打开所有options的游标

OPEN optionCursor

--指向第一行

FETCH NEXT FROM optionCursor INTO @USERID,@OPTIONBODY,@QUESTIONID,@OPTIONID

WHILE @@FETCH_STATUS = 0

BEGIN

--循环每条option数据，把每个问题的答案(option)拼接成一个字段

IF @QUESTIONID=1

BEGIN

IF @option1=''

SELECT @option1 = @OPTIONBODY

ELSE

SELECT @option1 = @option1 + ' | '+@OPTIONBODY

END

ELSE IF @QUESTIONID=2

BEGIN

IF @option2=''

SELECT @option2 = @OPTIONBODY

ELSE

SELECT @option2 = @option2 + ' | '+@OPTIONBODY

END

ELSE IF @QUESTIONID=3

BEGIN

IF @option3=''

SELECT @option3 = @OPTIONBODY

ELSE

SELECT @option3 = @option3 + ' | '+@OPTIONBODY

END

ELSE IF @QUESTIONID=4

BEGIN

IF @option4=''

SELECT @option4 = @OPTIONBODY

ELSE

SELECT @option4 = @option4 + ' | '+@OPTIONBODY

END

ELSE IF @QUESTIONID=5

BEGIN

IF @option5=''

SELECT @option5 = @OPTIONBODY

ELSE

SELECT @option5 = @option5 + ' | '+@OPTIONBODY

END

--设计问题时的失误，第6题第三项应该是第七题，此处用于纠正此错误

ELSE IF @QUESTIONID=6

BEGIN

IF (@OPTIONID=3)

SELECT @option7 = @OPTIONBODY

ELSE

BEGIN

IF @option6=''

SELECT @option6 = @OPTIONBODY

ELSE

SELECT @option6 = @option6 + ' | '+@OPTIONBODY

END

ELSE IF @QUESTIONID=7

BEGIN

IF(@OPTIONID=2)

SELECT @option9 = 'YES'--第七题第二项应该是第九题

ELSE

BEGIN

IF @option8=''--第七题第一项应该是第八题

SELECT @option8 = @OPTIONBODY

ELSE

SELECT @option8 = @option8 + ' | '+@OPTIONBODY

END

--更新临时表，完成每个报表数据(填充每个问题的答案)

UPDATE #TempTable set op1=@option1,op2=@option2,op3=@option3,op4=@option4,op5=@option5,op6=@option6,op7=@option7,op8=@option8,op9=@option9 WHERE [Id]=@id

--指向下一条option记录

FETCH NEXT FROM optionCursor INTO @USERID,@OPTIONBODY,@QUESTIONID,@OPTIONID

END

CLOSE optionCursor

DEALLOCATE optionCursor

--指向下一个user

FETCH NEXT FROM userCursor INTO @id,@firstName,@lastName,@address1,@address2,@city,@state,@zip,@birthday,@phone,@email,@CreatedDate

END

CLOSE userCursor

DEALLOCATE userCursor

SELECT

--[ID],

[FirstName],

[LastName],

[Address1],

[Address2],

[City],

[State],

[Zip],

''''+CONVERT(varchar, birthday,101) as birthday,

[phone],

[email],

''''+CONVERT(varchar, CreatedDate,100) as [CreatedDate],

ISNULL(op1, '') AS [Do you shop regularly for light bulbs at OSH?],

ISNULL(op2, '') AS [How often have you shopped at OSH for light bulbs in the past year?],

ISNULL(op3, '') AS [Why do you shop for light bulbs at OSH(select all reasons that apply)?],

ISNULL(op4, '') AS [What was the primary reason for your shopping trip to OSH?],

ISNULL(op5, '') AS [What other types of products, besides light bulbs did you purchase on your recent trip? (check ALL that apply)],

ISNULL(op6, '') AS [Are you?],

ISNULL(op7, '') AS [Age],

ISNULL(op8, '') AS [How many people in home?] ,

ISNULL(op9, '') AS [Check this box to receive special offers and information from OSH via email.]

FROM #TempTable

SET NOCOUNT OFF