SQL to transpose row pairs to columns in MS ACCESS database
A crosstab query should suit.
TRANSFORM First([Text]) AS LangText
SELECT ID, First([Text])
FROM Table
GROUP BY ID
PIVOT lang
Further information: http://allenbrowne.com/ser-67.html
SQL Query to transpose rows to columns in MS Access database
If you know up front how many workshops will be there and the number isn't significant I bet you could use aggregate function with if conditions:
select
partno
, itemname
, sum(iif(workshop = 'W101', stock, 0)) as w101
, sum(iif(workshop = 'Z239', stock, 0)) as z239
from t
group by partno, itemname
SQL for MS ACCESS linked table to transpose rows and columns
You can try the following thing.
From the starting position where you separated the two rows of data, no common key needed (since they're both one row).
SELECT DLookUp("F" & Number, "headers_qry") AS BU, DLookUp("F" & Number, "data_qry") As Sales
FROM (
SELECT DISTINCT Abs(Ones.ID Mod 10) + Abs(Tens.ID Mod 10) * 10 + Abs(Hundreds.ID Mod 10)*100 As Number
FROM MSysObjects As Ones, MSysObjects As Tens, MSysObjects As Hundreds
) As NumbersQuery
WHERE Number BETWEEN 1 And 200
Essentially, this query has 2 parts:
- A subquery that returns every number between 0 and 999 (technique adapted from Gustav)
- An outer query which returns the specified column number from both your queries, and filters the subquery.
The BETWEEN 1 And 200
can be adapted to fit your sample
If higher numbers are needed (0 to 9999) the subquery can also be adapted
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
Transpose to Count columns of Boolean values on Access SQL
you could just get individual totals per category:
SELECT COUNT(*) FROM STUDENTS WHERE HasBamboo
add them all up, and divide by
SELECT COUNT(*) FROM STUDENTS
It's not a great database design though... Better normalized would be:
Table Students; fields StudentID, StudentName
Table Plants; fields PlantID, PlantName
Table OwnedPlants; fields StudentID,PlantID
The last table then stores records for each student that owns a particular plant; but you could easily add different information at the right place (appartment number to Students; Latin name to Plants; date aquired to OwnedPlants) without completely redesigning table structure and add lots of fields. (DatAquiredBamboo, DateAquiredFlower, etc etc)
MS-Access Dynamically Convert Variable Row Values into Variable Column Values Using VBA
So with the help of a friend I figured it out. It turns out I needed two Functions because the one-to-many relationships go both directions in my case. I explain below what needs to happen in comments for this to work. Essentially I went with the second comment under the question I posed (pre-defining field names in static tables because there is a limited number of fields that any person will need - it can't exceed 256 fields anyway, but it isn't always practical to use more than a dozen or so fields - this way allows for both and at the same time to simplify the code significantly).
This solution actually works - but it's dependent on having tables (or queries in my situation) labeled ConvergeSend and ConvergeReceive. Also, it's important to note that the instances where the Destination is single and the Source is plural, the table or query (ConvergeSend/ConvergeReceive) must have the Destination value as a column TO THE LEFT of the iterated Source columns. This is also true (but reverse naming convention) for the other table/query (the Source column must be TO THE LEFT of the iterated Destination columns).
' For this code to work, create a table named "TransposedSend" with 8 columns: Source, Destination1, Destination2,...Destination7; OR however many you need
' Save the table, Edit it, change all field values to Number and remove the 0 as Default Value at the bottom
' Not changing the field values to Number causes the Insert Into function to append trailing spaces for no apparent reason
Public Function TransposeSend()
Dim i As Integer
Dim rs As DAO.Recordset, grp As DAO.Recordset
CurrentDb.Execute "DELETE * FROM TransposedSend", dbFailOnError
CurrentDb.Execute "INSERT INTO TransposedSend (Source) SELECT DISTINCT Source FROM ConvergeSend GROUP BY Source", dbFailOnError
Set grp = CurrentDb.OpenRecordset("SELECT DISTINCT Source FROM ConvergeSend GROUP BY Source")
grp.MoveFirst
Do While Not grp.EOF
Set rs = CurrentDb.OpenRecordset("SELECT Source, Destination, [Destination App Name] FROM ConvergeSend WHERE Source = " & grp(0))
i = 0
rs.MoveFirst
Do While Not rs.EOF
i = i + 1
CurrentDb.Execute "UPDATE TransposedSend SET Destination" & i & " = '" & rs(1) & "', [Destination" & i & " App Name] = '" & rs(2) & "'" & " WHERE Source = " & grp(0)
rs.MoveNext
Loop
grp.MoveNext
Loop
End Function
' For this code to work, create a table named "TransposedReceive" with 8 columns: Destination, Source1, Source2,...Source7; OR however many you need
' Save the table, Edit it, change all field values to Number and remove the 0 as Default Value at the bottom
' Not changing the field values to Number causes the Insert Into function to append trailing spaces for no apparent reason
Public Function TransposeReceive()
Dim i As Integer
Dim rs As DAO.Recordset, grp As DAO.Recordset
CurrentDb.Execute "DELETE * FROM TransposedReceive", dbFailOnError
CurrentDb.Execute "INSERT INTO TransposedReceive (Destination) SELECT DISTINCT Destination FROM ConvergeReceive GROUP BY Destination", dbFailOnError
Set grp = CurrentDb.OpenRecordset("SELECT DISTINCT Destination FROM ConvergeReceive GROUP BY Destination")
grp.MoveFirst
Do While Not grp.EOF
Set rs = CurrentDb.OpenRecordset("SELECT Destination, Source, [Source App Name] FROM ConvergeReceive WHERE Destination = " & grp(0))
i = 0
rs.MoveFirst
Do While Not rs.EOF
i = i + 1
CurrentDb.Execute "UPDATE TransposedReceive SET Source" & i & " = '" & rs(1) & "', [Source" & i & " App Name] = '" & rs(2) & "'" & " WHERE Destination = " & grp(0)
rs.MoveNext
Loop
grp.MoveNext
Loop
End Function
Related Topics
How to Create a Conditional Where Clause
Sql: How to Find Duplicates Based on Two Fields
SQL Select Max(Date) and Corresponding Value
Add Row Number to This T-SQL Query
SQL Server: How to Know If Any Row Is Referencing the Row to Delete
Oracle SQL: How to Use More Than 1000 Items Inside an in Clause
Calculate Row Wise Sum - SQL Server
How to Check If Identity_Insert Is Set to on or Off in SQL Server
How to Get the Raw Query String from Laravel's Query Builder Before Executing the Query
Why Isn't Row Level Security Enabled for Postgres Views
SQL Server Bulk Insert CSV with Data Having Comma
Postgresql: Fill Null Values in Timeserie Query with Previous Value
Having Transaction in All Queries