李三 男 销售经理 138xxxxxxxx
王四 女 客服经理 152xxxxxxxx
李三 男 客服经理 138xxxxxxxx
合并后:
姓名 性别 职务 联系电话
李三 男 销售经理/客服经理 138xxxxxxxx
王四 女 客服经理 152xxxxxxxx
6 个解决方案
#1
自定义函数:
select distinct 姓名, 性别, concateColumn("tName","姓名","职务",姓名,"/") as N职务 ,联系电话 from tName
Public Function concateColumn(sTable As String, sRow As String, sCol As String, vRow As String, Optional delimiter As String = "")
'合并(列转行) sTable 引用的表名; sRow 分组的字段名; sCol 列转行的字段名; vRow 分组的值; Optional 分隔符
Dim rs As New ADODB.Recordset
Dim sSQL As String
Dim sResult As String
Dim i As Integer
sResult = ""
sSQL = "select distinct " & sCol & " from " & sTable & " where " & sRow & "='" & vRow & "'"
rs.Open sSQL, CurrentProject.Connection, 1, 1
For i = 1 To rs.RecordCount
If i = rs.RecordCount Then
sResult = sResult & rs.Fields(0).Value
Else
sResult = sResult & rs.Fields(0).Value & delimiter
End If
rs.MoveNext
Next i
rs.Close
Set rs = Nothing
concateColumn = sResult
End Function
select distinct 姓名, 性别, concateColumn("tName","姓名","职务",姓名,"/") as N职务 ,联系电话 from tName
Public Function concateColumn(sTable As String, sRow As String, sCol As String, vRow As String, Optional delimiter As String = "")
'合并(列转行) sTable 引用的表名; sRow 分组的字段名; sCol 列转行的字段名; vRow 分组的值; Optional 分隔符
Dim rs As New ADODB.Recordset
Dim sSQL As String
Dim sResult As String
Dim i As Integer
sResult = ""
sSQL = "select distinct " & sCol & " from " & sTable & " where " & sRow & "='" & vRow & "'"
rs.Open sSQL, CurrentProject.Connection, 1, 1
For i = 1 To rs.RecordCount
If i = rs.RecordCount Then
sResult = sResult & rs.Fields(0).Value
Else
sResult = sResult & rs.Fields(0).Value & delimiter
End If
rs.MoveNext
Next i
rs.Close
Set rs = Nothing
concateColumn = sResult
End Function
#2
没有进入下面代码:
If i = rs.RecordCount Then
sResult = sResult & rs.Fields(0).Value
Else
sResult = sResult & rs.Fields(0).Value & delimiter
End If
rs.MoveNext
Next i
而且 rs.RecordCount 总是返回值为0;
这是怎么回事?
If i = rs.RecordCount Then
sResult = sResult & rs.Fields(0).Value
Else
sResult = sResult & rs.Fields(0).Value & delimiter
End If
rs.MoveNext
Next i
而且 rs.RecordCount 总是返回值为0;
这是怎么回事?
#3
只能 在ACCESS下运行
select 姓名,max(dc(姓名)) from tt group by 姓名
模块:
Function dc(ByVal dd As string) As String
f1 = ""
Set rs = CurrentDb.OpenRecordset("select 职务 from tt where 姓名='" & dd & "'")
Do While Not rs.EOF
f1 = f1 & rs(0) & "+"
rs.MoveNext
Loop
dc = Left(f1, Len(f1) - 1)
End Function
select 姓名,max(dc(姓名)) from tt group by 姓名
模块:
Function dc(ByVal dd As string) As String
f1 = ""
Set rs = CurrentDb.OpenRecordset("select 职务 from tt where 姓名='" & dd & "'")
Do While Not rs.EOF
f1 = f1 & rs(0) & "+"
rs.MoveNext
Loop
dc = Left(f1, Len(f1) - 1)
End Function
#4
谢谢!3楼的,你的方法可以用,得分稍后送上。
你说只能 在ACCESS下运行
那如何把合并后:
姓名 性别 职务 联系电话
李三 男 销售经理/客服经理 138xxxxxxxx
王四 女 客服经理 152xxxxxxxx
放回表中,同时删除重复的项呢?
你说只能 在ACCESS下运行
那如何把合并后:
姓名 性别 职务 联系电话
李三 男 销售经理/客服经理 138xxxxxxxx
王四 女 客服经理 152xxxxxxxx
放回表中,同时删除重复的项呢?
#5
select 姓名,max(dc(姓名)) into newtt from tt group by 姓名
删除旧表
删除旧表
#6
能再问下:这一个ACCESS 查询 如何用VC 操作?谢谢
#1
自定义函数:
select distinct 姓名, 性别, concateColumn("tName","姓名","职务",姓名,"/") as N职务 ,联系电话 from tName
Public Function concateColumn(sTable As String, sRow As String, sCol As String, vRow As String, Optional delimiter As String = "")
'合并(列转行) sTable 引用的表名; sRow 分组的字段名; sCol 列转行的字段名; vRow 分组的值; Optional 分隔符
Dim rs As New ADODB.Recordset
Dim sSQL As String
Dim sResult As String
Dim i As Integer
sResult = ""
sSQL = "select distinct " & sCol & " from " & sTable & " where " & sRow & "='" & vRow & "'"
rs.Open sSQL, CurrentProject.Connection, 1, 1
For i = 1 To rs.RecordCount
If i = rs.RecordCount Then
sResult = sResult & rs.Fields(0).Value
Else
sResult = sResult & rs.Fields(0).Value & delimiter
End If
rs.MoveNext
Next i
rs.Close
Set rs = Nothing
concateColumn = sResult
End Function
select distinct 姓名, 性别, concateColumn("tName","姓名","职务",姓名,"/") as N职务 ,联系电话 from tName
Public Function concateColumn(sTable As String, sRow As String, sCol As String, vRow As String, Optional delimiter As String = "")
'合并(列转行) sTable 引用的表名; sRow 分组的字段名; sCol 列转行的字段名; vRow 分组的值; Optional 分隔符
Dim rs As New ADODB.Recordset
Dim sSQL As String
Dim sResult As String
Dim i As Integer
sResult = ""
sSQL = "select distinct " & sCol & " from " & sTable & " where " & sRow & "='" & vRow & "'"
rs.Open sSQL, CurrentProject.Connection, 1, 1
For i = 1 To rs.RecordCount
If i = rs.RecordCount Then
sResult = sResult & rs.Fields(0).Value
Else
sResult = sResult & rs.Fields(0).Value & delimiter
End If
rs.MoveNext
Next i
rs.Close
Set rs = Nothing
concateColumn = sResult
End Function
#2
没有进入下面代码:
If i = rs.RecordCount Then
sResult = sResult & rs.Fields(0).Value
Else
sResult = sResult & rs.Fields(0).Value & delimiter
End If
rs.MoveNext
Next i
而且 rs.RecordCount 总是返回值为0;
这是怎么回事?
If i = rs.RecordCount Then
sResult = sResult & rs.Fields(0).Value
Else
sResult = sResult & rs.Fields(0).Value & delimiter
End If
rs.MoveNext
Next i
而且 rs.RecordCount 总是返回值为0;
这是怎么回事?
#3
只能 在ACCESS下运行
select 姓名,max(dc(姓名)) from tt group by 姓名
模块:
Function dc(ByVal dd As string) As String
f1 = ""
Set rs = CurrentDb.OpenRecordset("select 职务 from tt where 姓名='" & dd & "'")
Do While Not rs.EOF
f1 = f1 & rs(0) & "+"
rs.MoveNext
Loop
dc = Left(f1, Len(f1) - 1)
End Function
select 姓名,max(dc(姓名)) from tt group by 姓名
模块:
Function dc(ByVal dd As string) As String
f1 = ""
Set rs = CurrentDb.OpenRecordset("select 职务 from tt where 姓名='" & dd & "'")
Do While Not rs.EOF
f1 = f1 & rs(0) & "+"
rs.MoveNext
Loop
dc = Left(f1, Len(f1) - 1)
End Function
#4
谢谢!3楼的,你的方法可以用,得分稍后送上。
你说只能 在ACCESS下运行
那如何把合并后:
姓名 性别 职务 联系电话
李三 男 销售经理/客服经理 138xxxxxxxx
王四 女 客服经理 152xxxxxxxx
放回表中,同时删除重复的项呢?
你说只能 在ACCESS下运行
那如何把合并后:
姓名 性别 职务 联系电话
李三 男 销售经理/客服经理 138xxxxxxxx
王四 女 客服经理 152xxxxxxxx
放回表中,同时删除重复的项呢?
#5
select 姓名,max(dc(姓名)) into newtt from tt group by 姓名
删除旧表
删除旧表
#6
能再问下:这一个ACCESS 查询 如何用VC 操作?谢谢