Query To Tranform Rows To Column

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