CODEWITHSUNDEEP
sqlms-access
Matthew Edmond
Matthew Edmond

Reputation: 27

SQL match to new column

I am using SQL in Microsoft access. The table may contain more than one value for a bar code. I want to a query that returns one row with each unique bar code and columns for each of the first 5 values. Right now, I have a query returning the last value or the first value with min or max. How can I get the 2nd, 3rd, 4th, 5th value into separate columns?

Table:

table

Current query:

SELECT table.barcode, MIN(table.value)
FROM table
GROUP BY table.barcode

Current output:

current output

Goal output:

goal output

Upvotes: 0

Views: 39

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

This is rather tricky. I think you are stuck with conditional aggregation and a correlated subquery in MS Access. Note that the following assumes that the values are unique:

select barcode,
       max(iif(seqnum = 1, value, null)),
       max(iif(seqnum = 2, value, null)),
       max(iif(seqnum = 3, value, null)),
       max(iif(seqnum = 4, value, null)),
       max(iif(seqnum = 5, value, null))
from (select t.*,
             (select count(*)
              from t as t2
              where t2.barcode = t.barcode and t2.value <= t.value
             ) as seqnum
      from t
     ) t
group by barcode;

Upvotes: 2

Related Questions