Combinar Linhas / Concatenar Linhas

Eu estou procurando um Access 2007 equivalente à function COALESCE do SQL Server.

No SQL Server você poderia fazer algo como:

Pessoa

John Steve Richard 

SQL

 DECLARE @PersonList nvarchar(1024) SELECT @PersonList = COALESCE(@PersonList + ',','') + Person FROM PersonTable PRINT @PersonList 

Que produz: John, Steve, Richard

Eu quero fazer o mesmo, mas no Access 2007.

Alguém sabe como combinar linhas como esta no Access 2007?

Aqui está um exemplo de function definida pelo usuário (UDF) e uso possível.

Função:

 Function Coalsce(strSQL As String, strDelim, ParamArray NameList() As Variant) Dim db As Database Dim rs As DAO.Recordset Dim strList As String Set db = CurrentDb If strSQL <> "" Then Set rs = db.OpenRecordset(strSQL) Do While Not rs.EOF strList = strList & strDelim & rs.Fields(0) rs.MoveNext Loop strList = Mid(strList, Len(strDelim)) Else strList = Join(NameList, strDelim) End If Coalsce = strList End Function 

Uso:

 SELECT documents.MembersOnly, Coalsce("SELECT FName From Persons WHERE Member=True",":") AS Who, Coalsce("",":","Mary","Joe","Pat?") AS Others FROM documents; 

Uma versão do ADO, inspirada em um comentário por onedaywhen

 Function ConcatADO(strSQL As String, strColDelim, strRowDelim, ParamArray NameList() As Variant) Dim rs As New ADODB.Recordset Dim strList As String On Error GoTo Proc_Err If strSQL <> "" Then rs.Open strSQL, CurrentProject.Connection strList = rs.GetString(, , strColDelim, strRowDelim) strList = Mid(strList, 1, Len(strList) - Len(strRowDelim)) Else strList = Join(NameList, strColDelim) End If ConcatADO = strList Exit Function Proc_Err: ConcatADO = "***" & UCase(Err.Description) End Function 

De: http://wiki.lessthandot.com/index.php/Concatenate_a_List_into_a_Single_Field_%28Column%29

Eu acho que Nz é o que você está depois, a syntax é Nz(variant, [if null value]) . Aqui está o link da documentação: Função Nz

 ---Person--- John Steve Richard DECLARE @PersonList nvarchar(1024) SELECT @PersonList = Nz(@PersonList + ',','') + Person FROM PersonTable PRINT @PersonList 

Embora o Nz faça algo comparável ao COALESCE, você não pode usá-lo no Access para executar a operação que está executando. Não é o COALESCE que está construindo a lista de valores de linha, é a concatenação em uma variável.

Infelizmente, isso não é possível dentro de uma consulta do Access que precisa ser uma única instrução SQL e onde não há possibilidade de declarar uma variável.

Eu acho que você precisaria criar uma function que iria abrir um conjunto de resultados, iterar sobre ele e concatenar os valores de linha em uma string.

Para combinar linhas no Access, você provavelmente precisará de um código parecido com este:

 Public Function Coalesce(pstrTableName As String, pstrFieldName As String) Dim rst As DAO.Recordset Dim str As String Set rst = CurrentDb.OpenRecordset(pstrTableName) Do While rst.EOF = False If Len(str) = 0 Then str = rst(pstrFieldName) Else str = str & "," & rst(pstrFieldName) End If rst.MoveNext Loop Coalesce = str End Function 

Você vai querer adicionar código de tratamento de erros e limpar seu conjunto de registros, e isso vai mudar um pouco se você usar ADO em vez de DAO, mas a idéia geral é a mesma.

Eu entendo aqui que você tem uma tabela “pessoa” com 3 registros. Não há nada comparável ao que você descreve no Access.

No access “padrão” (conjunto de registros DAO), você terá que abrir um conjunto de registros e usar o método getrows para ter seus dados

 Dim rs as DAO.recordset, _ personList as String, _ personArray() as variant set rs = currentDb.open("Person") set personArray = rs.getRows(rs.recordcount) rs.close 

Uma vez que você tenha este array (ele será bidimensional), você pode manipulá-lo para extrair a “coluna” que você precisará. Pode haver uma maneira inteligente de extrair uma matriz unidimensional, para que você possa usar a instrução “Join” para concatenar cada valor da matriz em uma string.