Can you use SQL to mimic behavior of cursor type languages?

[vpellerito]

Is it possible to write a SQL select statement that grabs values from one or many fields (of a particular grouping ) and displays as a single concatenated field?

For example,

Project, Station, Question, SubQuestion, Response
KK-55, 10, 1, A, Boat
KK-55, 10, 1, B, House
KK-55, 10, 1, C, Cat
KK-55, 11, 1, A, Country
KK-55, 11, 2, A, Sand

Select statement shows:
Project, Station, Question, Response
KK-55, 10, 1, Boat-House-Cat
KK-55, 11, 1, Country
KK-55, 11, 2, Sand

I realize that it is not desirable to do this in databases because it would make the Response field non-atomic but for the sake of argument, is it possible?

This is a simple example of a larger question for me, which is can you use SQL to mimic the behavior of a cursor? This would be easy to do if I used VB to manipulate the data, for example.

 

[GingerR]

You can use VBA to get your result; loop thru the records and concatonate your text.

I do not know what this means:

I realize that it is not desirable to do this in databases because it would make the Response field non-atomic but for the sake of argument, is it possible?


Why isn't it desirable? If the client wants it, then they can have it. Also I do not know what you mean by "non-atomic". If you mean it won't be "normalized" or good db structure, well, you are not storing the data. The data is stored properly; what you want is to just display it concatonated like that.

I also do not understand this:
can you use SQL to mimic the behavior of a cursor
Do you mean using SQL to make the mouse cursor move around the screen?



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[dhookom]

vpellerito,
There is no Access query that can do this without running a couple queries or using VBA. Did you look in the FAQs for this forum?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[vpellerito]

OK. I figured as much.

I knew that it could be done by manipulating a recordset in VB because I have control over the "cursor" as it goes through a set of records and specific criteria but I didn't think it would be possible using straight SQL.

The FAQs faq701-3499 and faq701-4233 pretty much show how to handle this in VB. However, they leave the impression that the same task could be done simply using SQL but that it would be impractical. This was mainly a theoretical question.

Thanks GingerR and dhookom for your responses.

Vince

 

[dhookom]

This can be kinda accomplished with Access SQL. For instance, if you add a Products memo field to the Categories table in northwind.mdb, you can stick all the product names for each category into the field with SQL like:

UPDATE Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID SET Categories.Products = [Products] & "-" & [ProductName];

You would then need to run a query to remove the first "-".


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]