表结构：
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Log]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Log]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Users]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Users]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[options]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[options]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[questionAndAnswer]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[questionAndAnswer]
GO
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]
GO
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]
GO
CREATE TABLE [dbo].[options] (
[questionId] [int] NOT NULL ,
[optionId] [int] NOT NULL ,
[optionBody] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
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]
GO
存储过程：
CREATE PROCEDURE GetReport
@beginDate datetime,
@endDate datetime
AS
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 @beginDateand 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
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
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
GO
