SQL group by with delimiter

[modialsi]

Hi,

I have this view, where all data in the columns are the same (type,
model, serialnumber), except for the language column.
Now, what I need is the results, 1 line for all languages and the
languages like en-de-fr.

An illustration might clear up things:

Model, type, serialnumber, lang
jetta, vw, x1111, en
jetta, vw, x1111, fr

should become:

Model, type, serialnumber, lang
jetta, vw, x1111, en-fr

Is this possible is SQL (access 2000) and if so, how?

Kind regards,

Thomas

[Danny J. Lesandrini]

Re: SQL group by with delimiter

The only way I know to do this is through code. The routine attached at the
end of the post will run, so long as you have a reference to DAO. It creates
a table, tblOriginal, populates it with data similar to yours. Then it makes a
copy of that table and aggregates the "number" field for matching "names"

Danny J. Lesandrini


Option Compare Database
Option Explicit

Function FixTable()
On Error Resume Next

Dim db As DAO.Database
Dim strSQL As String
Dim rst As DAO.Recordset

Dim strColumn1 As String
Dim strColumn2 As String

Set db = CurrentDb()

' Delete Table, if exists
If DCount("*", "MsysObject s", "[Name]='tblOriginal'" ) = 1 Then
DoCmd.DeleteObj ect acTable, "tblOrigina l"
End If

strSQL = "CREATE TABLE tblOriginal (Column1 Text(10), Column2 Text(10)) "
db.Execute strSQL

strSQL = "INSERT INTO tblOriginal (Column1, Column2) VALUES ('A','1')"
db.Execute strSQL

strSQL = "INSERT INTO tblOriginal (Column1, Column2) VALUES ('A','2')"
db.Execute strSQL

strSQL = "INSERT INTO tblOriginal (Column1, Column2) VALUES ('B','1')"
db.Execute strSQL

strSQL = "INSERT INTO tblOriginal (Column1, Column2) VALUES ('B','2')"
db.Execute strSQL

strSQL = "INSERT INTO tblOriginal (Column1, Column2) VALUES ('B','3')"
db.Execute strSQL

strSQL = "INSERT INTO tblOriginal (Column1, Column2) VALUES ('C','1')"
db.Execute strSQL

' Delete Table, if exists
If DCount("*", "MsysObject s", "[Name]='tblCopy'") = 1 Then
DoCmd.DeleteObj ect acTable, "tblCopy"
End If

' Create Temp Table
strSQL = "SELECT Column1, Column2 INTO tblCopy " _
& "FROM tblOriginal WHERE 1 = 0;"
db.Execute strSQL

strSQL = "SELECT Column1, Column2 FROM tblOriginal " _
& "ORDER BY Column1, Column2 ASC"
Set rst = db.OpenRecordse t(strSQL, dbOpenSnapshot)

With rst
If Not .BOF And Not .EOF Then .MoveFirst

strColumn1 = !Column1
strColumn2 = !Column2

.MoveNext

Do Until .EOF
If strColumn1 = !Column1 Then
strColumn2 = strColumn2 & ", " & !Column2
Else
strSQL = "INSERT INTO tblCopy (Column1, Column2) " _
& "VALUES('" & strColumn1 & "','" & strColumn2 & "')"
db.Execute strSQL
strColumn1 = !Column1
strColumn2 = !Column2
End If
.MoveNext
Loop

' Insert Last Record
strSQL = "INSERT INTO tblCopy (Column1, Column2) " _
& "VALUES('" & strColumn1 & "','" & strColumn2 & "')"
db.Execute strSQL

.Close
End With

Set rst = Nothing
Set db = Nothing

DoCmd.OpenTable "tblCopy"

End Function


--

Danny J. Lesandrini
dlesandrini@hot mail.com

http://amazecreations.com/datafast/

"modialsi" <thomasflo@gmai l.com> wrote ...[color=blue]
> Hi,
>
> I have this view, where all data in the columns are the same (type,
> model, serialnumber), except for the language column.
> Now, what I need is the results, 1 line for all languages and the
> languages like en-de-fr.
>
> An illustration might clear up things:
>
> Model, type, serialnumber, lang
> jetta, vw, x1111, en
> jetta, vw, x1111, fr
>
> should become:
>
> Model, type, serialnumber, lang
> jetta, vw, x1111, en-fr
>
> Is this possible is SQL (access 2000) and if so, how?
>
> Kind regards,
>
> Thomas
>[/color]