Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Design Responses Table in survey Database

Author  Topic 

pamyral_279
Posting Yak Master

143 Posts
Posted - 2015-03-23 : 05:34:22
I have questions table and answers in survey database.
Now I design responses table from users !

CREATE TABLE [dbo].[Response](
[ResponseId] [int] IDENTITY(1,1) NOT NULL,
[SurveyId] [int] NULL,
[QuestionId] [int] NULL,
[AnswerId] [int] NULL,
[VoterId] [int] NULL,
[OtherAnswer] [nvarchar](max) NULL,
[AnswerType] [int] NULL,
[QuestionType] [int] NULL,
[flag] [char](10) NULL,
[Created_At] [datetime] NULL,
CONSTRAINT [PK_Response] PRIMARY KEY CLUSTERED
(
[ResponseId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


My Questions :
How can I store data if questions is multiply answers (checkbox type) ?What is best way ?

Thank you very much !

gbritton
Master Smack Fu Yak Hacker

2780 Posts
Posted - 2015-03-23 : 08:52:41
better to split it up into individual rows:

ReponseID
SurveyId
QuestionId
AnswerId
VoterId
Created_At

and have multiple rows for each respondent, one row for each question answered.
Go to Top of Page

pamyral_279
Posting Yak Master

143 Posts
Posted - 2015-03-23 : 10:35:12
quote:
Originally posted by gbritton

better to split it up into individual rows:

ReponseID
SurveyId
QuestionId
AnswerId
VoterId
Created_At

and have multiple rows for each respondent, one row for each question answered.



Are you sure ? Because i think Data redundancy is problem here !
Example :
ResponseID---SurveyID--QuestionId--AnswerId--VoterId
----1-----------1----------353--------51-------99
----2-----------1----------353--------52-------99
----3-----------1----------171--------16-------99
----4-----------1----------288--------22-------99
----5-----------1----------643--------64-------99
----6-----------1----------353--------53-------99

So that with questionid is 353 , we have 3 records with answerID are 51,52,53 ! If we have more fields in responses table ! so data redundancy is problem !
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts
Posted - 2015-03-23 : 10:46:14
That's not data redundancy, since all columns are Foreign Keys (or should be!) It simply comes a cross reference table with a date stamp.
Go to Top of Page

pamyral_279
Posting Yak Master

143 Posts
Posted - 2015-03-23 : 12:53:42
What do you think if I store data like :
ResponseID---SurveyID--QuestionId--AnswerId-------VoterId
----1-----------1----------353-----51|52|53----------99
----3-----------1----------171--------16-------------99
----4-----------1----------288--------22-------------99
----5-----------1----------643--------64-------------99

Is this easy to query data ? sorry i dont have much experiences with query database !
I think it will save alot of rows ! but im afraid that query problem !
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts
Posted - 2015-03-23 : 13:09:40
This: 51|52|53 will make querying hard. Better to have three separate rows as I suggested.
Go to Top of Page

pamyral_279
Posting Yak Master

143 Posts
Posted - 2015-03-23 : 13:31:28
quote:
Originally posted by gbritton

This: 51|52|53 will make querying hard. Better to have three separate rows as I suggested.


Ok ! Can you tell me sql statement which group data to display as my wish ! ( one line )
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts
Posted - 2015-03-23 : 13:43:31
not sure what you're looking for...
Go to Top of Page

pamyral_279
Posting Yak Master

143 Posts
Posted - 2015-03-23 : 13:48:59
quote:
Originally posted by gbritton

not sure what you're looking for...


I will store data as your suggest :
----1-----------1----------353--------51-------99
----2-----------1----------353--------52-------99
----3-----------1----------171--------16-------99
----4-----------1----------288--------22-------99
----5-----------1----------643--------64-------99
----6-----------1----------353--------53-------99

But I want to create query which display data:
----1-----------1----------353-----51|52|53----------99
----3-----------1----------171--------16-------------99
----4-----------1----------288--------22-------------99
----5-----------1----------643--------64-------------99
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts
Posted - 2015-03-23 : 14:23:28
something like this:


declare @ table (ResponseID int, SurveyID int, QuestionId int, AnswerId int, VoterId int)
insert into @(ResponseID,SurveyID,QuestionId,AnswerId,VoterId) values
--ResponseID---SurveyID--QuestionId--AnswerId--VoterId

(1, 1, 353, 51, 99),
(2, 1, 353, 52, 99),
(3, 1, 171, 16, 99),
(4, 1, 288, 22, 99),
(5, 1, 643, 64, 99),
(6, 1, 353, 53, 99)

select min(ResponseId) ResponseId,  SurveyID, QuestionId, stuff(AnswerIds,1,1,'') AnswerId, VoterId 
from @ t
cross apply 
(
	select '|' + cast(AnswerId as varchar(5))
	from @ _
	where _.SurveyID = t.SurveyID and _.QuestionId = t.QuestionId and _.VoterId = t.VoterId
	for xml path('')
)	_(AnswerIds)
group by SurveyID, QuestionId, AnswerIds, VoterId
order by ResponseId


Note that there are multiple ResponseIds since e.g. for q 353 there are three responses. If you want the top one
Go to Top of Page

pamyral_279
Posting Yak Master

143 Posts
Posted - 2015-03-23 : 22:19:08
quote:
Originally posted by gbritton

something like this:


declare @ table (ResponseID int, SurveyID int, QuestionId int, AnswerId int, VoterId int)
insert into @(ResponseID,SurveyID,QuestionId,AnswerId,VoterId) values
--ResponseID---SurveyID--QuestionId--AnswerId--VoterId

(1, 1, 353, 51, 99),
(2, 1, 353, 52, 99),
(3, 1, 171, 16, 99),
(4, 1, 288, 22, 99),
(5, 1, 643, 64, 99),
(6, 1, 353, 53, 99)

select min(ResponseId) ResponseId,  SurveyID, QuestionId, stuff(AnswerIds,1,1,'') AnswerId, VoterId 
from @ t
cross apply 
(
	select '|' + cast(AnswerId as varchar(5))
	from @ _
	where _.SurveyID = t.SurveyID and _.QuestionId = t.QuestionId and _.VoterId = t.VoterId
	for xml path('')
)	_(AnswerIds)
group by SurveyID, QuestionId, AnswerIds, VoterId
order by ResponseId


Note that there are multiple ResponseIds since e.g. for q 353 there are three responses. If you want the top one


It's cool ! ty so much !
Can you edit little bit for me ?
I want to add a field "myresponseid" to respond with answerid

ResponseID---SurveyID--QuestionId--AnswerId------VoterId--Myresponseid
----1-----------1----------353-----51|52|53----------99-----1|2|6
----3-----------1----------171--------16-------------99-----3
----4-----------1----------288--------22-------------99-----4
----5-----------1----------643--------64-------------99-----5

1|2|6 is ID of responseID ,
1 -> with answer ID 51
2 -> with answer ID 52
6 -> with answer ID 53
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts
Posted - 2015-03-24 : 07:16:58
Just take my example and see if you can do it
Go to Top of Page

pamyral_279
Posting Yak Master

143 Posts
Posted - 2015-03-24 : 12:07:06
quote:
Originally posted by pamyral_279

quote:
Originally posted by gbritton

something like this:


declare @ table (ResponseID int, SurveyID int, QuestionId int, AnswerId int, VoterId int)
insert into @(ResponseID,SurveyID,QuestionId,AnswerId,VoterId) values
--ResponseID---SurveyID--QuestionId--AnswerId--VoterId

(1, 1, 353, 51, 99),
(2, 1, 353, 52, 99),
(3, 1, 171, 16, 99),
(4, 1, 288, 22, 99),
(5, 1, 643, 64, 99),
(6, 1, 353, 53, 99)

select min(ResponseId) ResponseId,  SurveyID, QuestionId, stuff(AnswerIds,1,1,'') AnswerId, VoterId 
from @ t
cross apply 
(
	select '|' + cast(AnswerId as varchar(5))
	from @ _
	where _.SurveyID = t.SurveyID and _.QuestionId = t.QuestionId and _.VoterId = t.VoterId
	for xml path('')
)	_(AnswerIds)
group by SurveyID, QuestionId, AnswerIds, VoterId
order by ResponseId


Note that there are multiple ResponseIds since e.g. for q 353 there are three responses. If you want the top one


It's cool ! ty so much !
Can you edit little bit for me ?
I want to add a field "myresponseid" to respond with answerid

ResponseID---SurveyID--QuestionId--AnswerId------VoterId--Myresponseid
----1-----------1----------353-----51|52|53----------99-----1|2|6
----3-----------1----------171--------16-------------99-----3
----4-----------1----------288--------22-------------99-----4
----5-----------1----------643--------64-------------99-----5

1|2|6 is ID of responseID ,
1 -> with answer ID 51
2 -> with answer ID 52
6 -> with answer ID 53


Can you help me ?
Im not good at with sql and you can see all thread which i posted here and understand about that! Any help will be appreciate !
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts
Posted - 2015-03-24 : 12:47:11
I just added another cross apply to the query:


select min(ResponseId) ResponseId,  SurveyID, QuestionId, stuff(AnswerIds,1,1,'') AnswerId, VoterId, stuff(ResponseIDs,1,1,'') as MyResponseId
from @ t
cross apply 
(
	select '|' + cast(AnswerId as varchar(5))
	from @ _
	where _.SurveyID = t.SurveyID and _.QuestionId = t.QuestionId and _.VoterId = t.VoterId
	order by AnswerId
	for xml path('')
)	_(AnswerIds)
cross apply 
(
	select '|' + cast(ResponseID as varchar(5))
	from @ _
	where _.SurveyID = t.SurveyID and _.QuestionId = t.QuestionId and _.VoterId = t.VoterId
	order by ResponseId
	for xml path('')
)	_1(ResponseIDs)
group by SurveyID, QuestionId, AnswerIds, VoterId, ResponseIDs
order by ResponseId
Go to Top of Page

pamyral_279
Posting Yak Master

143 Posts
Posted - 2015-03-24 : 23:00:01
Thank you so much ! but seem that something wrong ! It is not follow into order !
I give you example :
declare @ table (ResponseID int, SurveyID int, QuestionId int, AnswerId int, VoterId int)
insert into @(ResponseID,SurveyID,QuestionId,AnswerId,VoterId) values
--ResponseID---SurveyID--QuestionId--AnswerId--VoterId

(1, 1, 353, 51, 99),
(2, 1, 353, 52, 99),
(3, 1, 171, 16, 99),
(4, 1, 288, 22, 99),
(5, 1, 643, 64, 99),
(6, 1, 353, 53, 99),
(8, 1, 643, 13, 99),
(96, 1, 643, 22, 99)

select min(ResponseId) ResponseId, SurveyID, QuestionId, stuff(AnswerIds,1,1,'') AnswerId, VoterId, stuff(ResponseIDs,1,1,'') as MyResponseId
from @ t
cross apply
(
select '|' + cast(AnswerId as varchar(5))
from @ _
where _.SurveyID = t.SurveyID and _.QuestionId = t.QuestionId and _.VoterId = t.VoterId
order by AnswerId
for xml path('')
) _(AnswerIds)
cross apply
(
select '|' + cast(ResponseID as varchar(5))
from @ _
where _.SurveyID = t.SurveyID and _.QuestionId = t.QuestionId and _.VoterId = t.VoterId
order by ResponseId
for xml path('')
) _1(ResponseIDs)
group by SurveyID, QuestionId, AnswerIds, VoterId, ResponseIDs
order by ResponseId


This is output :
1-------1------ 353--- 51|52|53------- 99----- 1|2|6
3------ 1------ 171--- 16------------ 99----- 3
4------ 1------ 288--- 22------------ 99----- 4
5------ 1------ 643--- 13|22|64------ 99----- 5|8|96

your result,and problem : 13|22|64 => 5|8|96
With answerId is 13 => result has to be :8
With answerId is 22 => result has to be :96
With answerId is 64 => result has to be :5

So that, order : 13|22|64 => 8|96|5

Can check for me ?
Thank you in million !
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts
Posted - 2015-03-25 : 08:46:05
You didn't mention before that you needed the responses in any particular order. I simply ordered them(see the order by clauses) by ReponseId). Change that order by to

order by AnswerId

that should do the trick.
Go to Top of Page

pamyral_279
Posting Yak Master

143 Posts
Posted - 2015-03-26 : 04:03:06
declare @ table (ResponseID int, SurveyID int, QuestionId int, AnswerId int, VoterId int)
insert into @(ResponseID,SurveyID,QuestionId,AnswerId,VoterId) values
--ResponseID---SurveyID--QuestionId--AnswerId--VoterId

(1, 1, 353, 51, 99),
(2, 1, 353, 52, 99),
(3, 1, 171, 16, 99),
(4, 1, 288, 27, 99),
(5, 1, 643, 64, 99),
(6, 1, 353, 53, 99),
(8, 1, 643, 13, 99),
(96, 1, 643, 22, 99),
(96, 1, 99, 22, 99),
(597, 1, 99, 2, 99),
(198, 1, 99, 42, 99),
(5, 1, 743, 54, 99),
(5, 1, 443, 24, 99)

select min(ResponseId) ResponseId, SurveyID, QuestionId, stuff(AnswerIds,1,1,'') AnswerId, VoterId, stuff(ResponseIDs,1,1,'') as MyResponseId
from @ t
cross apply
(
select '|' + cast(AnswerId as varchar(5))
from @ _
where _.SurveyID = t.SurveyID and _.QuestionId = t.QuestionId and _.VoterId = t.VoterId
order by AnswerId
for xml path('')
) _(AnswerIds)
cross apply
(
select '|' + cast(ResponseID as varchar(5))
from @ _
where _.SurveyID = t.SurveyID and _.QuestionId = t.QuestionId and _.VoterId = t.VoterId
order by AnswerId
for xml path('')
) _1(ResponseIDs)
group by SurveyID, QuestionId, AnswerIds, VoterId, ResponseIDs
order by ResponseId


It's perfect ! ty so much ...... and thank you in million !
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site