access合并多行列数据到一行,类似于mysql的group_concact()函数

来源:网络 责任编辑:栏目编辑 发表时间:2013-07-01 11:05 点击:

Concatenate fields in same table
Author(s)
Dev Ashish


(Q)    I need to concatenate a field in the format "Value1; Value2; Value3" etc. for each unique value of another field in the same table.  How can I do this?
(A)    Using the fConcatFld function,  in the Northwind database, the following query should return a concatenated list of all CustomerIDs if you group by ContactTitle.
使用方式:
SELECT ContactTitle, fConcatFld("Customers","ContactTitle","CustomerID","string",[ContactTitle]) AS Customers FROM Customers GROUP BY ContactTitle;
 
参数说明:
fConcatFld参数说明

stTable As String 表名称
 _stForFld As String,  查询的条件字段名称
_stFldToConcat As String,  合并的字段名称
_ stForFldType As String, 合并字段的类型
_vForFldVal As Variant 合并字段的查询条件
'************ Code Start **********
'This code was originally written by Dev Ashish
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Dev Ashish
'


Function fConcatFld(stTable As String, _
                    stForFld As String, _
                    stFldToConcat As String, _
                    stForFldType As String, _
                    vForFldVal As Variant) _
                    As String
'Returns mutiple field values for each unique value
'of another field in a single table
'in a semi-colon separated format.
'
'Usage Examples:
'   ?fConcatFld(("Customers","ContactTitle","CustomerID", _
'                "string","Owner")
'Where  Customers     = The parent Table
'       ContactTitle  = The field whose values to use for lookups
'       CustomerID    = Field name to concatenate
'       string        = DataType of ContactTitle field
'       Owner         = Value on which to return concatenated CustomerID
'


Dim lodb As Database, lors As Recordset
Dim lovConcat As Variant, loCriteria As String
Dim loSQL As String
Const cQ = """"

    On Error GoTo Err_fConcatFld
   
    lovConcat = Null
    Set lodb = CurrentDb
   
    loSQL = "SELECT [" & stFldToConcat & "] FROM ["
    loSQL = loSQL & stTable & "] WHERE "
   
    Select Case stForFldType
        Case "String":
            loSQL = loSQL & "[" & stForFld & "] =" & cQ & vForFldVal &a

    相关新闻>>

      发表评论
      请自觉遵守互联网相关的政策法规,严禁发布色情、暴力、反动的言论。
      用户名: 验证码:点击我更换图片
      最新评论 更多>>

      推荐热点

      • Request.ServerVariables 参数大全
      • 执行全文索引时出现权限不足的解决方法
      • 导入excel文件处理流程节点的解决方案
      • 查看sql修改痕迹(SQL Change Tracking on Table)
      • MongoDB安装为Windows服务方法与注意事项
      • App数据层设计及云存储使用指南
      • PostgreSQL启动过程中的那些事三:加载GUC参数
      • 写给MongoDB开发者的50条建议Tip1
      • Percolator与分布式事务思考(二)
      网站首页 - 友情链接 - 网站地图 - TAG标签 - RSS订阅 - 内容搜索
      Copyright © 2008-2015 计算机技术学习交流网. 版权所有

      豫ICP备11007008号-1