Pivot on multiple fields and export from Access
In your question you say that
I have a way to generate a report that looks like the following
and then list the data as
Batch Zone Value1 Value2
----- ---- ------ ------
25 1 5 15
25 2 12 31
26 1 6 14
26 2 10 32
Now perhaps the data may already be in "un-pivoted" form somewhere (with different Value
s in separate rows), but if not then you would use something like the following query to achieve that
SELECT
[Batch],
"Zone" & [Zone] & "_" & "Value1" AS [ValueID],
[Value1] AS [ValueValue]
FROM BatchDataByZone
UNION ALL
SELECT
[Batch],
"Zone" & [Zone] & "_" & "Value2" AS [ValueID],
[Value2] AS [ValueValue]
FROM BatchDataByZone
...returning:
Batch ValueID ValueValue
----- ------------ ----------
25 Zone1_Value1 5
25 Zone2_Value1 12
26 Zone1_Value1 6
26 Zone2_Value1 10
25 Zone1_Value2 15
25 Zone2_Value2 31
26 Zone1_Value2 14
26 Zone2_Value2 32
However you get to that point, if you save that query as [BatchDataUnpivoted] then you could use a simple Crosstab Query to "string out" the values for each batch...
TRANSFORM Sum(BatchDataUnpivoted.[ValueValue]) AS SumOfValueValue
SELECT BatchDataUnpivoted.[Batch]
FROM BatchDataUnpivoted
GROUP BY BatchDataUnpivoted.[Batch]
PIVOT BatchDataUnpivoted.[ValueID];
...returning...
Batch Zone1_Value1 Zone1_Value2 Zone2_Value1 Zone2_Value2
----- ------------ ------------ ------------ ------------
25 5 15 12 31
26 6 14 10 32
Access 2013: Pivot multiple columns
TRANSFORM
can only occur at the start of an SQL statement.
First unpivot all the years and only then pivot the metric. Now, as MS Access apparently
has its limits when it comes to complex queries (many unions), it is best to pass via
an intermediate table:
SELECT FileIDRunID, Metric, Year, Value
FROM (
SELECT FileIDRunID, Metric, 1999 As Year, [1999] As Value
FROM DataTable
UNION ALL
SELECT FileIDRunID, Metric, 2000 As Year, [2000] As Value
FROM DataTable
UNION ALL
SELECT FileIDRunID, Metric, 2001 As Year, [2001] As Value
FROM DataTable
UNION ALL
SELECT FileIDRunID, Metric, 2002 As Year, [2002] As Value
FROM DataTable
)
INTO myTempTable;
I have union
-ed 4 years, but you should check how far you can go before MS Access refuses the statement for being too complex.
Then, add some more years, with the same number of union
s:
INSERT INTO myTempTable (FileIDRunID, Metric, Year, Value)
SELECT FileIDRunID, Metric, Year, Value
FROM (
SELECT FileIDRunID, Metric, 2003 As Year, [2003] As Value
FROM DataTable
UNION ALL
SELECT FileIDRunID, Metric, 2004 As Year, [2004] As Value
FROM DataTable
UNION ALL
SELECT FileIDRunID, Metric, 2005 As Year, [2005] As Value
FROM DataTable
UNION ALL
SELECT FileIDRunID, Metric, 2006 As Year, [2006] As Value
FROM DataTable
);
etc... to finally do this:
TRANSFORM FIRST(Value)
SELECT FileIDRunID,
Year
FROM myTempTable
GROUP BY FileIDRunID,
Year
PIVOT Metric
And then drop the temporary table and compress the database to get the original size back. If possible, redesign the database to use the completely unpivoted structure as in the temporary table.
Transform multiple rows into columns with unique key
Indoors today waiting out the hurricane, so I figured I'd create this custom solution. Answers for all of these steps are found elsewhere, but it was not simple to sort through all of the contrived solutions so I hope this answer is more useful all around.
The basic answer for changing the rows to columns is here. But unlike the this question's data, the sample data for that answer was already properly sequenced. As long as the column values [ID] and [Email] form unique pairs and have NO NULL values, then its possible to generate the proper sequencing using an aggregate subquery or a call to an Access aggregate function. To reduce the overall number of queries, I went ahead and generated the transformed column names in the same query. (If the values are not unique or have null values, the sequencing will be off and the final results will be missing some data.)
The second challenge here is that there are two columns that need to be transformed, but the Access SQL Transform statement (i.e. the Crosstab query) only allows one transform column per query. Creating two separate queries and then joining them is rather simple, but since the Crosstab queries generate a dynamic (i.e. undetermined) number of columns, it is not really possible to interleave the Email and Comments columns without manual effort of picking each column in order. Further, explicitly specifying which columns are in the query destroys the dynamic aspect of the crosstab queries and will either leave additional column out, or it will generate an error if the overall number of columns is reduced.
UPDATE: Just after posting the original solution (now labeled Solution 2), I realized that I could resolve the column interleaving problem by actually going backward one step... first generate more rows --one row for each email address and a separate row for each comment-- before the final transform placing them on the same row altogether again.
Solution 1
Save the following query and name it [Sequenced]. To facilitate the correct column ordering in the final output, I used the word 'Remark' instead of 'Comment' since it sorts after 'Email':
SELECT Data.ID, Data.Email, Data.Comments,
1 + DCount("[ID]","Data","[ID]=" & [ID] & " and [Email]<'" & Replace([Email],"'","''") & "'") AS SeqNum,
Format([SeqNum],"000") & ' Email' AS EmailColumn,
Format([SeqNum],"000") & ' Remark' AS CommentsColumn
FROM Data
ORDER BY Data.ID, Data.Email;
Save the following query and name it [Backwards]:
SELECT ID, EmailColumn AS ColumnName, Email AS [Value]
FROM Sequenced
UNION SELECT ID, CommentsColumn AS ColumnName, Comments AS [Value]
FROM Sequenced
ORDER BY [ID], [ColumnName];
Save the following query and name it [InterleavedCrosstab]:
TRANSFORM First(Backwards.Value) AS FirstOfValue
SELECT Backwards.ID
FROM Backwards
GROUP BY Backwards.ID
ORDER BY Backwards.ID, Backwards.ColumnName
PIVOT Backwards.ColumnName;
Solution 2
Save the following query and name it [Sequenced2]:
SELECT Data.ID, Data.Email, Data.Comments,
1 + DCount("[ID]","Data","[ID]=" & [ID] & " and [Email]<'" & Replace([Email],"'","''") & "'") AS SeqNum,
'Email' & Format([SeqNum],"000") AS EmailColumn,
'Comments' & Format([SeqNum],"000") AS CommentsColumn
FROM Data
ORDER BY Data.ID, Data.Email;
Save the following query and name it [EmailCrosstab]:
TRANSFORM First(Sequenced2.Email) AS FirstOfEmail
SELECT Sequenced2.ID
FROM Sequenced2
GROUP BY Sequenced2.ID
ORDER BY Sequenced2.ID
PIVOT Sequenced2.EmailColumn;
Save the following query and name it [CommentsCrosstab]:
TRANSFORM First(Sequenced2.Comments) AS FirstOfComments
SELECT Sequenced2.ID
FROM Sequenced2
GROUP BY Sequenced2.ID
ORDER BY Sequenced2.ID
PIVOT Sequenced2.CommentsColumn;
Finally, the most general result query will return ALL columns, but they will not be interleaved and there will duplicate [ID] columns:
SELECT EmailCrosstab.*,
CommentsCrosstab.*
FROM CommentsCrosstab INNER JOIN EmailCrosstab
ON CommentsCrosstab.ID = EmailCrosstab.ID;
Here's a prettified version but only has up to 3 email and comment columns:
SELECT EmailCrosstab.ID,
EmailCrosstab.Email001,CommentsCrosstab.Comments001,
EmailCrosstab.Email002,CommentsCrosstab.Comments002,
EmailCrosstab.Email003,CommentsCrosstab.Comments003
FROM CommentsCrosstab INNER JOIN EmailCrosstab
ON CommentsCrosstab.ID = EmailCrosstab.ID;
Solution 3
I had already typed up the following VBA procedure when I realized the query-only solution was rather easy, so here's a bonus alternative.
Public Sub CustomTransform()
'* This code assumes that the field values
'* [ID] and [Email] constitute a unique pair
'* and that there are NO NULL values.
Dim i As Integer, MaxIDRows As Integer
Dim IDSeq As Integer
Dim lastID As Long
Dim IDstring As String
Dim tbl As TableDef
Dim idx As Index
Dim db As Database
Dim rsSrc As Recordset2, rsResult As Recordset2
Const resultTable As String = "Customer Crosstab"
Set db = CurrentDb
MaxIDRows = db.OpenRecordset( _
"SELECT Max(Counter.Rows) AS MaxRows" & _
" FROM ( SELECT Count(Data.[ID]) AS [Rows]" & _
" FROM Data GROUP BY Data.[ID]) AS Counter" _
).Fields(0).Value
'* Column count <= 254 : ID + N * (Email + Comment columns)
If MaxIDRows = 0 Then
MsgBox "No data.", vbOKOnly Or vbExclamation, "No Data"
Exit Sub
ElseIf MaxIDRows >= 252 / 2 Then
MsgBox "Maximum number of columns exceeded.", _
vbOKOnly Or vbExclamation, "Maximum Columns Exceeded"
Exit Sub
End If
On Error Resume Next
db.TableDefs.Delete resultTable
Err.Clear
On Error GoTo 0
Set tbl = db.CreateTableDef(resultTable)
With tbl
' Create fields and append them to the new TableDef
' object. This must be done before appending the
' TableDef object to the TableDefs collection of the
' Northwind database.
.Fields.Append .CreateField("ID", dbLong)
For i = 1 To MaxIDRows
IDstring = Format(i, "000")
.Fields.Append .CreateField("Email" & IDstring, dbText, 255)
.Fields.Append .CreateField("Comments" & IDstring, dbText, 255)
Next
Set idx = .CreateIndex("Primary Key")
idx.Fields.Append idx.CreateField("ID")
idx.Primary = True
.Indexes.Append idx
End With
db.TableDefs.Append tbl
Set rsResult = db.OpenRecordset(resultTable, dbOpenTable)
Set rsSrc = db.OpenRecordset( _
"SELECT ID, Email, Comments" & _
" FROM Data" & _
" ORDER BY ID, Email")
lastID = -1
Do Until rsSrc.EOF
If rsSrc!id <> lastID Then
If lastID <> -1 Then
rsResult.Update
End If
IDSeq = 0
rsResult.AddNew
rsResult!id = rsSrc!id
End If
lastID = rsSrc!id
IDSeq = IDSeq + 1
IDstring = Format(IDSeq, "000")
rsResult.Fields("Email" & IDstring) = rsSrc!email
rsResult.Fields("Comments" & IDstring) = rsSrc!Comments
rsSrc.MoveNext
Loop
rsSrc.Close
If rsResult.EditMode <> dbEditNone Then
rsResult.Update
End If
rsResult.Close
Debug.Print "CustomTransform Done"
End Sub
Pivot Query in MS Access
Consider:
TRANSFORM First(Data.Cat) AS FirstOfCat
SELECT Data.ID, Data.Name
FROM Data
GROUP BY Data.ID, Data.Name
PIVOT "Cat" & DCount("*","Data","ID=" & [ID] & " AND Cat<'" & [Cat] & "'")+1;
Or if there is a unique record identifier field - autonumber should serve:
TRANSFORM First(Data.Cat) AS FirstOfCat
SELECT Data.ID, Data.Name
FROM Data
GROUP BY Data.ID, Data.Name
PIVOT "Cat" & DCount("*","Data","ID=" & [ID] & " AND ID_PK<" & [ID_PK])+1;
Access Union/Pivot to Swap Columns and Rows
This will have to be a two-step process to transform. First you will have to rotate the data in your current query to be in rows instead of columns, then you will have to transform the dates into columns instead of rows.
The query will be something like this:
TRANSFORM max(val) as MaxValue
SELECT col
FROM
(
SELECT [Date], '# Insp' as Col, [# Insp] as val
FROM yourQuery
UNION ALL
SELECT [Date], '# Passed' as Col, [# Passed] as val
FROM yourQuery
UNION ALL
SELECT [Date], '# Failed' as Col, [# Failed] as val
FROM yourQuery
UNION ALL
SELECT [Date], '% Acceptance' as Col, [% Acceptance] as val
FROM yourQuery
)
GROUP BY col
PIVOT [Date]
I am guessing the your current query is saved in your database, you will replace the yourQuery
in my example with the name of your query.
I just tested this in MS Access 2003 with the values in your sample above and it produced the result you want.
Related Topics
SQL Server Table Creation Date Query
When Should I Use Primary Key or Index
Converting Int to Real in SQLite
How to Simulate Deadlock on SQL Server
How to Find a Default Constraint Using Information_Schema
How to Get the Current Year Using SQL on Oracle
Calculate Execution Time of a SQL Query
SQL Query for a Carriage Return in a String and Ultimately Removing Carriage Return
Generate Insert SQL Statements from a CSV File
View or Temporary Table - Which to Use in Ms SQL Server
How to Check Any Missing Number from a Series of Numbers
SQL Distinct for 2 Fields in a Database
Db2 Query to Retrieve All Table Names for a Given Schema
How to Trim a String in SQL Server Before 2017