Johny Bravo
asked on
Help with Sql Query
Hi Experts,
I have below query(output of it attached in quiz1.jpg)
I need to change this query to display output as quizquery.bmp
The optionA/B/C/D column should come in rows under one column.
Your help is really appreciated.
Thanks
quiz1.JPG
quizquery.bmp
I have below query(output of it attached in quiz1.jpg)
Select Qid as Queid,QText as Question,
OptionA,OptionB,OptionC,OptionD,QAns as Answer
from examQB eq inner join Exam e
on eq.examid = e.examid
where e.ExamId = @ExamId and eq.isDeleted = 0 and e.isdeleted = 0
order by newid()
I need to change this query to display output as quizquery.bmp
The optionA/B/C/D column should come in rows under one column.
Your help is really appreciated.
Thanks
quiz1.JPG
quizquery.bmp
ASKER
Hi PortletPaul,
I am using C#. Can you tell how to do this in C#.
Arrangement is not an issue. I am binding this to gridview. This works.
But I need the output( I don't mind if it is done in C#) as given in fig 2.
Then the dataset should like it.
I thought it might be easier to get the formatted output from sql .
I am using C#. Can you tell how to do this in C#.
Arrangement is not an issue. I am binding this to gridview. This works.
But I need the output( I don't mind if it is done in C#) as given in fig 2.
Then the dataset should like it.
I thought it might be easier to get the formatted output from sql .
I would advise to allow sql to provide the data in separate columns - massaging into a single columns isn't a great thing to do. (i.e. yes it can be done - but I advise not to)
I cannot do your C# stuff - but that is certainly where you do the "arrangement"
(! including the transposition of columns to rows)
I cannot do your C# stuff - but that is certainly where you do the "arrangement"
(! including the transposition of columns to rows)
I think u can make use of PIVOT in sql server
as I said - it can be done - but should it? SQL natively (see quiz1.JPG) will present the question and each option as separate columns - when the data is received by C# it must be massaged into a format for consumption by the end-user. That massaging should also do the transposition. into whatever layout is required.
I'll stop now - if others wish to provide the data in a single column - please go ahead - I just think its a bad idea (i.e. let sql do its thing & let C# do its thing).
I'll stop now - if others wish to provide the data in a single column - please go ahead - I just think its a bad idea (i.e. let sql do its thing & let C# do its thing).
if OBJECT_ID('a') is not null
drop table a
go
create table a
(
Qid int,
QText varchar(20)
,OptionA varchar(20)
,Optionb varchar(20)
,Optionc varchar(20)
,Optiond varchar(20)
,QAns varchar(20)
)
declare @count int=1
while @count <=20
begin
insert into a
select @count,'text'+ltrim(STR(@count)),'A'+ltrim(STR(@count)),'B'+ltrim(STR(@count)),'C'+ltrim(STR(@count)),'D'+ltrim(STR(@count)),'ans'+ltrim(STR(@count))
set @count+=1
end
--- origional text
Select Qid as Queid,QText as Question,
OptionA,OptionB,OptionC,OptionD,QAns as Answer
from a
--- Excepted format
Select Qid as Queid,QText as Question,
options,QAns as Answer
from a
unpivot ( value for options in (OptionA,OptionB,OptionC,OptionD) )pvt
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I Agree with PortletPaul that its a bad idea to do such tasks with SQL.
C# is best way to handle such situations.
I don't know much in ASP.Net, but I think Repeater can manage them in wanted format.
C# is best way to handle such situations.
I don't know much in ASP.Net, but I think Repeater can manage them in wanted format.
http://www.codeproject.com/Articles/44274/Transpose-a-DataTable-using-C
Transpose a DataTable using C#
the right way in C# (example above)
:( still think this is a very bad idea (below)
the following may work for the 'unpivot' and should perform better than multiple unions (and yes even against the unpivot - but on such a small scale it probably won't be noticeable)
QID SEQ DETAIL
111 A To be or not to be, that is the question.
111 B to be
111 C not to be
111 D both
111 E neither
111 F to be
Transpose a DataTable using C#
the right way in C# (example above)
:( still think this is a very bad idea (below)
the following may work for the 'unpivot' and should perform better than multiple unions (and yes even against the unpivot - but on such a small scale it probably won't be noticeable)
SELECT
Qid AS Queid
, CA.seq
, CA.detail
FROM examQB eq
INNER JOIN Exam e ON eq.examid = e.examid AND e.isdeleted = 0
CROSS APPLY (
VALUES
('A', QText)
, ('B', OptionA)
, ('C', OptionB)
, ('D', OptionC)
, ('E', OptionD)
, ('F', QAns)
) as CA (seq, detail)
WHERE eq.ExamId = @ExamId
AND eq.isDeleted = 0
however I expect the output will look like this:QID SEQ DETAIL
111 A To be or not to be, that is the question.
111 B to be
111 C not to be
111 D both
111 E neither
111 F to be
ASKER
Thank you all the experts for taking time to look into the issue
With what technology are you actually displaying the questions? (e.g. PHP, C#, java)
Arranging a question like this:
Q1: This is the question
Opt: 2 der
Opt: 3 eh?
Opt: 4 nuh!
Opt: 5 all the above
this is better handled in a "presentation layer" not sql.