SQL Query Tough One For me !

[Luckyman1]

Hi there,
I have this query I'm trying to do in SQL. Help is greatly appericaited.

Was trying in .net but got an unknow error

TABLE:- trn_question_dtl
========================================================
trn_question_key 1
question_nbr 1
question_desc What is a Counselor?

trn_question_key 2
question_nbr 2
question_desc What is the function of a counselor?

trn_question_key 3
question_nbr 3
question_desc In your opionon what is the most fundamental idea of the twen
thith

trn_question_key 4
question_nbr 3
question_desc century? Taking into account that the main focus of this que
stion

trn_question_key 5
question_nbr 3
question_desc is to see how more thatn one line of detail is displayed on y
our com

trn_question_key 6
question_nbr 3
question_desc puter screen. Have at it.
=======================================================

I would like to have output something like this:-


=============================================================================
1 What is a Counselor?
2 What is the function of a counselor?
3 In your opionon what is the most fundamental idea of the twenthith century? Taking into account that the main focus of this question is to see how more thatn one line of detail is displayed on your computer screen. Have at it.
=============================================================================

As you can see question_nbr has to printed distinctively and last four record have thehir question_desc slapped together because they match the question_nbr as 3

A query for this would be sooo helpful...
Your help is very much appericaited..

-lucky-

 

[r937]

the sql is straightforward, and simply returns the rows with ORDER BY question_nbr, trn_question_key

the "slapping together" must be done with application code

rudy

http://r937.com/

SQL Consulting

 

[SQLSister]

Why not chnage your table structure so that the field takes enough characters to put the question in one record? In SQL Server, varchar takes up to 8000 characters and text can take many more than that. I'm sure other databases also have a n equivalent data type which will serve your needs.

Questions about posting. See faq183-874

 

[Luckyman1]

Thankyou for the Answers.
I cannot change the schema of the database. Its the database people choice :-|

The application code for this has to be done in java. I'm trying now to writout that part.

Any helpful ideas or snippet I can get started with...would be helpful.

Thanks!
-Lucky-

 

[SQLSister]

Well the only way I can think of is to create a temp table and then loop through the records one at a time and concatentate to the appropriate record if it exists or add a new record if it doesn't. This however is very inefficient and may well take a very long time depending on the number of records you have. You can use a cursor to do this is if your database supports cursors. Of course neither temp tables nor cursors tend to be implmented the same from database to database, so ANSI SQL might not be an option here. Then you will have to manipulate it at the user interface level.

Unless this is a COTS product, I would have a long discussion with your database people about the inefficency of the structure and the continuing problems you will have querying it. I might even make them build this query so they understand why it's a problem.



Questions about posting. See faq183-874

 

[r937]

COTS?

rudy

http://r937.com/

SQL Consulting

 

[r937]

okay, never mind

commercial off the shelf

google is your friend

rudy

http://r937.com/

SQL Consulting

 

[SQLSister]

Sorry, I've dealt with so many COTS produts through the years, I forget everyone doesn;t know the acronym.

Questions about posting. See faq183-874

 

[Luckyman1]

Hi I have the following application code for to do what i've described above. I'm at a loss. Help is very appericaited.

public ArrayList RetriveQuestions()
{
SList = dataTestCtrl.RetriveQuestions();
int size = SList.length;
tempe="";
j = 0;



for (int i=0; i < size ; ++i)
{
if (SList.getquestion_nbr().equals(tempe))
{
SList[j].setquestion_desc(SList[j].getquestion_desc()+SList.getquestion_desc());
SList[j].setquestion_nbr("");
j= j + 1;
}
else
{
j = j + 1;
}
tempe=SList.getquestion_nbr();
}
return Util.arrayToArrayList(SList);

}
============================
I get the following results:-

1 What is a Counselor?
2 What is the function of a counselor?
3 In your opionon what is the most fundamental idea of the twenthith
century? Taking into account that the main focus of this question century? Taking into account that the main focus of this question
3 is to see how more thatn one line of detail is displayed on your co
puter screen. Have at it. puter screen. Have at it.

=====================================================

As you can see in 2nd coloms questions are repeated twice.
in the 3 question and afterwards.


==================================
My goal is to have this output:-

1 What is a Counselor?
2 What is the function of a counselor?
3 In your opionon what is the most fundamental idea of the twenthith century? Taking into account that the main focus of this question is to see how more thatn one line of detail is displayed on your computer screen. Have at it.
========================================================
Please tell me what I'm doing wrong here...
Thankyou !!!

-Lucky-