Convert Row Data into Columns using SQL on MS Access

Good day,

Initially I had a table that works like this:

Table1

PartSN Test Data
Part1 Test1 20
Part1 Test2 23

I could convert the test into column using MAX IIF like

Select
Table1.PartSN,
Max(IIF(Table1.Test = 'Test1', Table1.Data, NULL)) As 'Test1',
Max(IIF(Table1.Test = 'Test2', Table1.Data, NULL)) As 'Test2'
From Table1 Group By Table1.PartSN

Data would be

PartSN Test1 Test2
Part1   20     23

Now my problem is that my test includes multiple iteration

PartSN Test Data Iter
Part1  Test1 20   1
Part1  Test2 23   1
Part1  Test1 19   2
Part1  Test2 24   2

Using my previous code, including iter in the IIF would only allow me to get one row but i need all iterations present like this:

PartSN Iter Test1 Test2
Part1   1    20     23
Part1   2    19     24

Thank you for any help or advice or link to a possible solution.

2 answers

  • answered 2018-05-16 11:14 Gordon Linoff

    Just include it in the select and group by:

    select t1.PartSN, t1.iter,
           max(iif(t1.Test = 'Test1', t1.Data, NULL)) As Test1,
           max(iif(t1.Test = 'Test2', t1.Data, NULL)) As Test2
    from Table1 as t1
    group by t1.PartSN, t1.iter;
    

  • answered 2018-05-16 14:20 Parfait

    Consider MS Access' unique crosstab query (unavailable in other DBMS) which avoids the line by line conditional aggregation expressions and automates the pivots across all Test values:

    TRANSFORM Max(t.Data) As MaxData
    SELECT t.PartSN, t.Iter
    FROM mytable t
    GROUP BY t.PartSN, t.Iter
    PIVOT t.Test;
    
    -- PartSN   Iter    Test1   Test2
    -- Part1    1       20      23
    -- Part1    2       19      24