Get No. of times a colum of a table appears in ano

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

SQL Server 2000 Forums

Transact-SQL (2000)

Get No. of times a colum of a table appears in ano

Author

Topic

coolcyber
Starting Member

7 Posts

Posted - 2006-03-13 : 09:59:53

Hi i have two tables.

CREATE TABLE [tblSurvey2_Questions] (
[QuestionID] [int] IDENTITY (1, 1) NOT NULL ,
[SurveyID] [int] NULL ,
[QuestionNum] [int] NULL ,
[Question] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AnswerType] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[QuizFlag] [bit] NULL ,
[RequiredFlag] [bit] NULL ,
[Choice1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Choice2] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Choice3] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Choice4] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Choice5] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Choice6] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Choice7] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Choice8] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Choice9] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Choice10] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Choice11] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Choice12] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Choice13] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Choice14] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Choice15] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Choice16] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Choice17] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Choice18] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Choice19] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Choice20] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CorrectChoice] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Answers1] [int] NULL ,
[Answers2] [int] NULL ,
[Answers3] [int] NULL ,
[Answers4] [int] NULL ,
[Answers5] [int] NULL ,
[Answers6] [int] NULL ,
[Answers7] [int] NULL ,
[Answers8] [int] NULL ,
[Answers9] [int] NULL ,
[Answers10] [int] NULL ,
[Answers_Correct] [int] NULL ,
[TimesAnswered] [int] NULL ,
CONSTRAINT [PK_tblSurvey2_Questions] PRIMARY KEY CLUSTERED
(
[QuestionID]
ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [tblSurvey2_Answers] (
[UserAnswerID] [int] IDENTITY (1, 1) NOT NULL ,
[QuestionID] [int] NULL ,
[SurveyID] [int] NULL ,
[UserID] [int] NULL ,
[Answer] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CreateDate] [datetime] NULL ,
[ModifyDate] [datetime] NULL ,
CONSTRAINT [PK_tblSurvey2_UserAnswers] PRIMARY KEY CLUSTERED
(
[UserAnswerID]
ON [PRIMARY]
) ON [PRIMARY]
GO

here, if the question is of type checkbox/radio i have to find how manytimes each choice appears in the answers table. I did this using multiple calls from vb .net but it takes forever. The approach i followed was:

1) first find all the rows of type ('radio', 'checkbox') in tblSurvey2_questions

2) in a Loop: find each time its corresponding choice appears in the tblsurvey2_answers table

3) Calculate % of times it appears. END LOOP

This takes forever, and the request times out for like 15 rows itself.

So i tired using a sp, but i got stuck there. I got till finding the column name, but after that, im not able to go how to proceed. sp till where i got is:

declare @sql1 varchar(255)
declare @i int
declare @checkvalues varchar(255)
declare @checkvalues1 varchar(255)
set @i=1
set @checkvalues = 'as'
--print @checkvalues
set @checkvalues1 = 'asd'
--print @checkvalues1

while len(@checkvalues1) >0 -- (@i < 20)
begin

select @checkvalues = 'choice'+convert(varchar(1), @i) from tblSurvey2_questions where questionID = 7
--set @sql1 =' select ' + @checkvalues1+' = ltrim(rtrim('+@checkvalues+')) from tblSurvey2_questions where questionID = 7'
set @sql1 ='select choice'+convert(varchar(1), @i)+' from tblSurvey2_questions where questionID = 7'

--select @checkvalues from tblSurvey2_questions where questionID = 7
exec(@sql1)

print @checkvalues
--print @checkvalues1
set @i = @i +1
if @i =10
break
else
continue

end

Any help is appreciated.

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-13 : 10:02:38

Normalise the table
Read this
http://www.datamodel.org/NormalizationRules.html

Madhivanan

Failing to plan is Planning to fail

coolcyber
Starting Member

7 Posts

Posted - 2006-03-13 : 10:10:50

cannot be normalized now. even with normalization there is not much to be gained as most of the choice are all unique.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-03-13 : 10:51:23

There is a LOT to be gained with normalization. No need for dozens of lines of VB to process things one by one, just simple 1 line SQL statemetns to get whatever data you need.

Always, always, always normalize your data. It's one thing to say "it cannot be normalized now" if it really cannot be, but it's another thing to think that normalzing data will not give you any advantages.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources