Sorting table in SQL or Access

Question

Code  | FName | Freq
---------------------
L1    | Ted   | 4
L1    | Frank | 
L1    | Ted   | 5
L2    | Ted   | 7
L1    | Jim   | 4
L1    | Ted   | 7
L4    | Ed    | 1
L2    | Ted   | 2
L1    | Frank | 2

Lets say I have a table like that in my database. Would it possible to grab the records in SQL (or grab in SQL then reorganize in another Access query) so that I can get record set that looks like this:

Code | FName | Freq
-------------------------
L1    | Ted   | 4, 5, 7
L1    | Frank | 2
L2    | Ted   | 2, 7
L4    | Ed    | 1
L1    | Jim   | 4

where the different 'Freq' numbers are appended together in a list based off 'Code' AND 'Fname'? Order doesn't matter as long as all the different freqency numbers are in the list. I can assume that for each combination of Code and Fname that there will be distinct, non-repeated frequency numbers (so a combination of 'L1' and 'Ted' will never have a freq value of 4 more than once)

In MS Access, this can only be done with a UDF : http://stackoverflow.com/questions/8601643/access-sql-query-to-concatenate-rows/8603245#8603245 — Fionnuala, Aug 13 '12 at 21:47

score 1 · Accepted Answer · answered Aug 13 '12 at 21:55

1

Then you are in luck!

select code, fname, listagg(cast(freq as varchar(32), ',') within group (order by freq)
from t
group by code, fname

I'm not sure what this does with the blank, however.

answered Aug 13 '12 at 21:55

Gordon Linoff

1,242,037
58
646
786

Sorting table in SQL or Access

1 Answers1