CODEWITHSUNDEEP
sqlms-access
wildfire
wildfire

Reputation: 119

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.

Upvotes: 1

Views: 67

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

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;

Upvotes: 1

Parfait
Parfait

Reputation: 107632

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

Upvotes: 0

Related Questions