Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Help with Sql Query

Posted on 2013-06-13
10
Medium Priority
?
499 Views
Last Modified: 2013-06-14
Hi Experts,

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() 

Open in new window


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
0
Comment
Question by:johny_bravo1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39243970
>>The optionA/B/C/D column should come in rows under one column.
With what technology are you actually displaying the questions? (e.g. PHP, C#, java)

Arranging a question like this:

Q1: This is the question
Opt1: blah
Opt: 2 der
Opt: 3 eh?
Opt: 4 nuh!
Opt: 5 all the above
Answer = [       ]        

this is better handled in a "presentation layer" not sql.
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 39243981
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 .
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39243987
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)
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 8

Expert Comment

by:vinurajr
ID: 39244035
I think u can make use of PIVOT in sql server
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39244048
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).
0
 
LVL 2

Expert Comment

by:vivekkumarSharma
ID: 39244059
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

Open in new window

0
 
LVL 19

Accepted Solution

by:
Bhavesh Shah earned 2000 total points
ID: 39244063
hi,

I don't know in C#, but see this query, if it fits to your criteria.

 Select Qid as Queid,QText as Question,        
 OptionA 'Option',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       
union
Select Qid as Queid,QText as Question,        
 OptionB,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       
union
Select Qid as Queid,QText as Question,        
 OptionC,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       
union
Select Qid as Queid,QText as Question,        
 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() 

Open in new window

0
 
LVL 2

Expert Comment

by:vivekkumarSharma
ID: 39244065
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.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39244125
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)
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

Open in new window

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
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 39247099
Thank you all the experts for taking time to look into the issue
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question