Hi Leonid, The query might have answer somewhere in Pivot table , but i am not too sure. following are my tables and i want to build a dynamic view table or procedure or function, which ever applicable. They tables are created for a simple survey tool with single or multichoice questions.

   CREATE TABLE [dbo].[Questions](
    [QuestionID] [numeric](18, 0) IDENTITY(1,1) NOT NULL Primary Key,
    [QuestionTitle] [nvarchar](500) NULL,
... Other Columns.

CREATE TABLE [dbo].[Choices](
    [ChoiceID] [numeric](18, 0) IDENTITY(1,1) NOT NULL Primary Key,
    [ChoiceTitle] [nvarchar](500) NULL,
    [FKQuestionID] [numeric](18, 0) NULL )

CREATE TABLE [dbo].[SurveyResponseMaster](
    [ResponseID] [numeric](18, 0) IDENTITY(1,1) NOT NULL Primary Key,
    [FKSurveyID] [numeric](18, 0) NULL,
    [UserName] [nvarchar](100) NULL,
    [UserLocation] [nvarchar](100) NULL ...
other columns....

CREATE TABLE [dbo].[SurveyResponses](
    [ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL Primary key,
    [FKResponseID] [numeric](18, 0) NULL,
    [FKSurveyID] [numeric](18, 0) NULL,
    [FKQuestionID] [numeric](18, 0) NULL,
    [FKChoiceID] [numeric](18, 0) NULL )

SurveyResponseMaster captures individual's information and SurveyResponses records his vote given for each questions's choices.

Now I would like to build a table that looks like below. '1' represents user did vote for that choice.
ChoiceID will come from 'Choices' table and it does not matter to which question the choice belongs to. it should just list all choices available for that survey as columns and add '1' where ever user voted for that choice as recorded in SurveyResponses table.

 ResponseID FKSurveyID	UserName	UserLocation	ChoiceId-1	ChoiceId-2 ChoiceId-3	……	ChoiceId-n
1   1	ABC	Mumbai	0	1	0		0
2   1	PQR	Delhi	1	0	0		0
3   1	XYZ	Mumbai	1	1	0		0

Please Advice with same sample.

Regards

PS. We are using SQL Server 2005.
If there is much better and easy way to do this in SQL2008, pls let us know. we do have license of sql2008, but currently we have not been using it. so I would prefer sql2005 solution.

Submitted under: Microsoft SQL Server · TSQL ·  ·  ·