How do I create a new table in Access without duplicates in the PROD_ID field based on a table that has duplicates in this field? I would like a table like this: Note: PROD_ID; ATC_COD_1 and ATC_COD_2 are the field names. Starting with table like this: Note: PROD_ID; ATC_COD are the field names. There always will be only two records

How to create a new table without duplicates in a field, based on a table that has duplicates in this field?

How do I create a new table in Access without duplicates in the PROD_ID field based on a table that has duplicates in this field?

I would like a table like this:

╔═════════╦═══════════╦═══════════╗
║ PROD_ID ║ ATC_COD_1 ║ ATC_COD_2 ║
╠═════════╬═══════════╬═══════════╣
║      65 ║ D01AC02   ║ D10AE01   ║
║      71 ║ R01BA52   ║ R06AX07   ║
║     101 ║ N02BE51   ║ M03BX05   ║
╚═════════╩═══════════╩═══════════╝

Note: PROD_ID; ATC_COD_1 and ATC_COD_2 are the field names.

Starting with table like this:

╔═════════╦═════════╗
║ PROD_ID ║ ATC_COD ║
╠═════════╬═════════╣
║      65 ║ D01AC02 ║
║      65 ║ D10AE01 ║
║      71 ║ R01BA52 ║
║      71 ║ R06AX07 ║
║     101 ║ N02BE51 ║
║     101 ║ M03BX05 ║
╚═════════╩═════════╝

Note: PROD_ID; ATC_COD are the field names.

There always will be only two records for each PROD_ID and I don’t need any criteria.

Answer

You can use aggregation:

select PROD_ID,
       min(ATC_COD) as ATC_COD_1,
       if(min(ATC_COD) <> max(ATC_COD), max(ATC_COD), null) as ATC_COD_2 
from t
group by PROD_ID

Advertisement

Answer