关于Access查询的巨难问题，求各位大佬帮忙了

[hillseok]

现有表如下
Name Value
aaa abc
aaa cde
aaa dfg
bbb rte
bbb dls
先要求查询结果如下
aaa abc,cde,dfg
bbb rte,dls
求sql语句，一句不行多句也行，只求能做出来

[hillseok]

我是要在access上运行的，所以请不要使用存储过程

[zarge]

不知道access能不能自定义函数，这种情况在sql中一般是用自定义函数实现的

[vegetarian1]

巨难

[Softlee81307]

up

[zjcxc]

ACCESS建立一个模块,在模块中写处理函数就行了.

[lxysjl]

UP

[suxming]

将楼主的数据表为t名.下面的SQL就可运行了.
select name,iif(col1<>null,col1,'')+ iif(col2<>null,','+col2,'') + iif(col3<>null,','+col3,'')
+ iif(col4<>null,','+col4,'')+iif(col5<>null,','+col5,'')+ iif(col6<>null,','+col6,'')
+ iif(col7<>null,','+col7,'')+ iif(col8<>null,','+col8,'')+ iif(col9<>null,','+col9,'')
+ iif(col10<>null,','+col10,'') as value1
from
(select name
,max(iif( num mod 10 = 1,iif(rtrim(ltrim(o.value))<>null,rtrim(ltrim(o.value)),''),'')) as col1
,max(iif( num mod 10= 2,iif(rtrim(ltrim(o.value))<>null,rtrim(ltrim(o.value)),''),'')) as col2
,max(iif( num mod 10 = 3,iif(rtrim(ltrim(o.value))<>null,rtrim(ltrim(o.value)),''),'')) as col3
,max(iif( num mod 10 = 4,iif(rtrim(ltrim(o.value))<>null,rtrim(ltrim(o.value)),''),'')) as col4
,max(iif( num mod 10 = 5,iif(rtrim(ltrim(o.value))<>null,rtrim(ltrim(o.value)),''),'')) as col5
,max(iif( num mod 10 = 6,iif(rtrim(ltrim(o.value))<>null,rtrim(ltrim(o.value)),''),'')) as col6
,max(iif( num mod 10 = 7,iif(rtrim(ltrim(o.value))<>null,rtrim(ltrim(o.value)),''),'')) as col7
,max(iif( num mod 10 = 8,iif(rtrim(ltrim(o.value))<>null,rtrim(ltrim(o.value)),''),'')) as col8
,max(iif( num mod 10 = 9,iif(rtrim(ltrim(o.value))<>null,rtrim(ltrim(o.value)),''),'')) as col9
,max(iif( num mod 10 = 0,iif(rtrim(ltrim(o.value))<>null,rtrim(ltrim(o.value)),''),'')) as col10
from
(select count(a.value) as num,a.name,a.value
from t as a,t as b
where a.value>=b.value
group by a.name,a.value
)as o
group by o.name
) as a

[suxming]

name value
aaa abc,cde,dfg
bbb ,dls,rte

这是查询结果.头尾的逗号自己处理吧.要下班了.没时间了.

[suxming]

下雨.烦,走不了.
处理逗号 iif(mid(value1,1,1)<>',',value1,mid(value1,2,len(value1)))
其实用where a.value>=b.value是不对的.应该用表的主键才对.但楼主表中没有出现
主键只好用value字段了.如果value值相同就会出错.

[zjcxc]

'创建一个模块,添加如下函数

Public Function f_getstr(bh$, fd$) As String 'bh为Name,fd为要组合的字段名value
Dim iRe As String, Re As DAO.Recordset

Set Re = CurrentDb.OpenRecordset("select " & fd & " from 合并字符串 where name='" + bh + "'")
While Re.EOF = False
iRe = iRe & "," & Re(0)
Re.MoveNext
Wend
Re.Close

f_getstr = Mid(iRe, 2)
End Function

[zjcxc]

--这样调用

SELECT name,f_getstr(name,"value") AS value
FROM 合并字符串
GROUP BY name


假设表名为合并字符串

[poonzsp]

study

[hillseok]

谢谢各位的热心解答，我已经单纯的用sql语句解决了，不过感觉做法很傻，创建了好多临时表，有空试试大家的方法