I have a 3 column table, and some columns have the same attributes. I want to return a table that shows which columns each attribute appears in. I am using SQL in Microsoft Access for this. How can I do this? Just an idea of where to start would be helpful as I do not have much SQL experience.

Table I have:

Table I want to return:

So far I have been doing this manually, and it is not working as the tables I am working with hold large data sets.

CodePudding user response：

You can use some VBA to do this. Something like:

Sub sUnpivot()
    On Error GoTo E_Handle
    Dim db As DAO.Database
    Dim rsSteer As DAO.Recordset
    Dim rsData As DAO.Recordset
    Dim rsLookup As DAO.Recordset
    Dim strSQL As String
    Set db = CurrentDb
' Start by getting a unique list across the 3 columns
    strSQL = "SELECT Fruits1 AS Fruits FROM tblPivot " _
        & " UNION SELECT Fruits2 FROM tblPivot " _
        & " UNION SELECT Fruits3 FROM tblPivot;"
    Set rsSteer = db.OpenRecordset(strSQL)
    If Not (rsSteer.BOF And rsSteer.EOF) Then
        db.Execute "DELETE * FROM tblUnpivot;"    ' get rid of old data
        Set rsData = db.OpenRecordset("SELECT * FROM tblUnpivot WHERE 1=2;")
        Do
            With rsData
                .AddNew
                !Attribute = rsSteer!Fruits
                strSQL = "SELECT COUNT(*) FROM tblPivot WHERE Fruits1='" & rsSteer!Fruits & "';"
                Set rsLookup = db.OpenRecordset(strSQL)
                If rsLookup(0) > 0 Then !Fruits1 = "x"
                strSQL = "SELECT COUNT(*) FROM tblPivot WHERE Fruits2='" & rsSteer!Fruits & "';"
                Set rsLookup = db.OpenRecordset(strSQL)
                If rsLookup(0) > 0 Then !Fruits2 = "x"
                strSQL = "SELECT COUNT(*) FROM tblPivot WHERE Fruits3='" & rsSteer!Fruits & "';"
                Set rsLookup = db.OpenRecordset(strSQL)
                If rsLookup(0) > 0 Then !Fruits3 = "x"
                .Update
            End With
            rsSteer.MoveNext
        Loop Until rsSteer.EOF
    End If
sExit:
    On Error Resume Next
    rsLookup.Close
    rsSteer.Close
    rsData.Close
    Set rsLookup = Nothing
    Set rsSteer = Nothing
    Set rsData = Nothing
    Set db = Nothing
    Exit Sub
E_Handle:
    MsgBox Err.Description & vvbcrlf & vbCrLf & "sUnpivot", vbOKOnly   vbCritical, "Error: " & Err.Number
    Resume sExit
End Sub

CodePudding user response：

We unpivot to get all the fruit types in one column and then pivot to group and mark them by fruit1, fruit2 and fruit3.

with t2 as 
(
          select fruits1 as fruit, 'fruits1' as col from t
union all select fruits2,          'fruits2'        from t
union all select fruits2,          'fruits3'        from t
)

select   fruit
        ,count(case when col = 'fruits1' then 1 end) as fruits1
        ,count(case when col = 'fruits2' then 1 end) as fruits2
        ,count(case when col = 'fruits3' then 1 end) as fruits3


from     t2
group by fruit

Fiddle