Ab-normalization at Experts Exchange - part 21

Question today: sql crosstab with auto-generated column headers?

How to transform:







Into:

Company	A - Firstname	A - SurName	B - Firstname	B - SurName	C - Firstname	C - SurName	D - Firstname	D - SurName
comp1	Harry	Smith	Ellen	Black	Jack	Johnson
comp2	Norman	White					Ann	Wright

Well I must admit that the question was located in the "coding" section, nevertheless capricorn1 once again proved to be a “coding happy” amateur instead of a professional.

His solution was this VBA code creating an additional table with the result:

Sub CreateTable()
Dim rs As DAO.Recordset, sFld As String, rs1 As DAO.Recordset, rsNew As DAO.Recordset
Dim j As Integer, fldArr()
Set rs = CurrentDb.OpenRecordset("select distinct contacttype from table1 order by contacttype")
rs.MoveFirst
Do Until rs.EOF
sFld = sFld & "," & rs(0) & "_FirstName Text(25)" & "," & rs(0) & "_SurName Text(25)"
rs.MoveNext
Loop
rs.Close
'see if the table already exists, if so, delete the table
If DCount("*", "msysobjects", "[name]='newTable'") > 0 Then
CurrentDb.Execute "drop table newTable"
End If
'create the table
CurrentDb.Execute "Create Table newTable(Company Text(25) " & sFld & ")"

Set rsNew = CurrentDb.OpenRecordset("newTable")
For j = 0 To rsNew.Fields.Count - 1
ReDim Preserve fldArr(j)
fldArr(j) = rsNew(j).Name
Next

Set rs = CurrentDb.OpenRecordset("select distinct company from table1 order by company")
rs.MoveFirst
Do Until rs.EOF
Set rs1 = CurrentDb.OpenRecordset("select * from table1 where company='" & rs(0) & "' order by ContactType")
rs1.MoveFirst
rsNew.AddNew
rsNew("Company") = rs1("Company")
Do Until rs1.EOF
For j = 1 To UBound(fldArr)
If Left(fldArr(j), 1) = rs1("ContactType") And Mid(fldArr(j), InStr(fldArr(j), "_") + 1) = "FirstName" Then
rsNew(fldArr(j)) = rs1("FirstName")
End If
If Left(fldArr(j), 1) = rs1("ContactType") And Mid(fldArr(j), InStr(fldArr(j), "_") + 1) = "SurName" Then
rsNew(fldArr(j)) = rs1("SurName")
End If
Next
rs1.MoveNext
Loop
rsNew.Update
rs.MoveNext
Loop
rs.Close
rs1.Close
rsNew.Close
End Sub


While all needed using a query would have been:

TRANSFORM Max(qryBasicData.FirstName) AS MaxOfFirstName
SELECT qryBasicData.Company
FROM (SELECT Table1.Company, [ContactType] & " - Firstname" AS Type, Table1.FirstName
FROM Table1
UNION
SELECT Table1.Company, [ContactType] & " - SurName" AS Type, Table1.SurName
FROM Table1) qryBasicData
GROUP BY qryBasicData.Company
PIVOT qryBasicData.Type;

Needless to say that a query is at least 10 times faster and won’t let the database grow with adding (and deleting) a temporary table.


Nic;o)
Still proud to be suspended from this "quality site" where wannabee experts rule and "quality" is just another word from the dictionary.
(The question can be found at: http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Coding-Macros/Q_26108122.html)