sql for distinct record 1

[ramonzulueta]

Hi. Good day, can you please give me some direction on my SQL statement

If you have got an access database table with some data that look something like this:

id letter
1 a
2 a
2 b
3 c
4 a
4 b
4 c

How do you make that the recordset will look like this(what would be the sql statement for this please):

id letter
1 a
2 a,b
3 c
4 a,b,c

i would like to pull those values of database field 'letter' and write them using asp according to the field 'id'. thanks very much indeed.

 

[traingamer]

see faq701-3499

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[dhookom]

There is also a generic concatenate function with sample usage at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

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]

 

[cmmrfrds]

You could try a query something like this.

SELECT X.id,
ltrim(Max(X.A1) + iif(Max(X.B1)>"",", ","") + Max(X.B1) + iif(Max(X.A1)>""and Max(X.B1)>"" ,", ","") + max(X.C1)) AS letters
FROM
[SELECT A.id, IIf(A.letter="a",letter," ") as A1, IIf(A.letter="b",letter," ") as B1, IIf(A.letter="c",letter," ") as C1
FROM LetterTable AS A
]. AS X
GROUP BY X.id;

 

[ramonzulueta]

it worked! thanks very much cmmrfrds and to all of you guys who shared their time. you have been a great help

 

[dhookom]

Do you ever expect to have letter "d" or "e"? My experience is where there are a, b, and c, then d and e will soon follow.

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]