Hi
Your help for the following query will be highly apprecaited. I've
wasted alot of time on it. Data definition is at the bottom.
Thanks
-----------------------------------
Business need: User selects answers for few questions. Answers are
presented in the form of radio buttons.
Questions Table:
===================
QuestionID QuestionName
1 1.Rate your organization
2 10.Opportunity in your group
3 117.Effectiveness
----------------------------------------
Answers Table:
==============
AnswerId AnswerName QuestionID
1 1.Best 1
2 2.Average 1
3 3.Wrose 1
4 1.Hardly Any 2
5 2.not too much 2
6 3.Average 2
7 4.Great Deal 2
8 1.Strong 3
9 2.Minor 3
10 3.Nothing 3
--------------------------------------------------------
Lets say User ABC answered below:
For Question 1, she chose '2.Average'
For Question 2: she left blank
FOr Quesiton 3: she chose '2.Minor'
And for user XYZ:
For Question 1, she let blank
For Question 2: she left blank
FOr Quesiton 3: she chose '3.Nothing'
Then the Results table will look like this:
Results Table:
===============
Resultsid QuestionID AnswerId User
1 1 2 ABC
2 3 9 ABC
3 3 10 XYZ
---------------------------------------------------
Desired Output:
===============
User 1 10 117
--- -- --- ----
ABC 2 2
XYZ 3
Explanation:
--> Show ALL distinct users on rows
--> show ALL distinct QuestionName on the columns BUT only display the
number before '.'
--> Show the chosen answer (answername) for each user but only display
the number before '.'
-------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Questions]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[Questions]
GO
CREATE TABLE [dbo].[Questions] (
[QuestionId] [int] IDENTITY (1, 1) NOT NULL ,
[QuestionName] [nvarchar] (1000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
-------------------------------
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Answers]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[Answers]
GO
CREATE TABLE [dbo].[Answers] (
[AnswerId] [int] IDENTITY (1, 1) NOT NULL ,
[AnswerName] [nvarchar] (150) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[QuestionId] [int] NULL
) ON [PRIMARY]
GO
----------------------------------
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Results]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[Results]
GO
CREATE TABLE [dbo].[Results] (
[ResultId] [int] IDENTITY (1, 1) NOT NULL ,
[QuestionId] [int] NULL ,
[AnswerId] [int] NULL ,
[UserId] [nchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY]
GO
---------------------------------------------
INSERT Answers (
AnswerName ,
QuestionId
)
VALUES (
'1.Best' ,
1
)
go
INSERT Answers (
AnswerName ,
QuestionId
)
VALUES (
'2.Average' ,
1
)
go
INSERT Answers (
AnswerName ,
QuestionId
)
VALUES (
'3.Wrose' ,
1
)
go
INSERT Answers (
AnswerName ,
QuestionId
)
VALUES (
'1.Hardly Any' ,
2
)
go
INSERT Answers (
AnswerName ,
QuestionId
)
VALUES (
'2.not too much' ,
2
)
go
INSERT Answers (
AnswerName ,
QuestionId
)
VALUES (
'3.Average' ,
2
)
go
INSERT Answers (
AnswerName ,
QuestionId
)
VALUES (
'4.Great Deal' ,
2
)
go
INSERT Answers (
AnswerName ,
QuestionId
)
VALUES (
'1.Strong' ,
3
)
go
INSERT Answers (
AnswerName ,
QuestionId
)
VALUES (
'2.Minor' ,
3
)
go
INSERT Answers (
AnswerName ,
QuestionId
)
VALUES (
'3.Nothing' ,
3
)
go
-----------------------------
INSERT Questions (
QuestionName
)
VALUES (
'1.Rate your organization'
)
go
INSERT Questions (
QuestionName
)
VALUES (
'10.Opportunity in your group'
)
go
INSERT Questions (
QuestionName
)
VALUES (
'117.Effectiveness'
)
go
------------------------------------
INSERT Results (
QuestionId ,
AnswerId ,
UserId
)
VALUES (
1 ,
2 ,
'ABC'
)
go
INSERT Results (
QuestionId ,
AnswerId ,
UserId
)
VALUES (
3 ,
9 ,
'ABC'
)
go
INSERT Results (
QuestionId ,
AnswerId ,
UserId
)
VALUES (
3 ,
10 ,
'XYZ'
)
go
