CODEWITHSUNDEEP
sqlms-access
Karvy1
Karvy1

Reputation: 1055

MS Access: Transposing a database table to avoid redundancies

I have a database table with 2 columns as below:

IMPORT_ID   EXPORT_ID
SI1313721   SI1440839
SI1313721   SI1440997
SI1313722   SI1440672
SI1313722   SI1440776
SI1313722   SI1441313

IMPORT_IDs are getting repeated (SI1313721 - 2 times and SI1313722 - 3 times). I want to transpose this table into

SI1313721   SI1440839   SI1440997
SI1313722   SI1440672   SI1440776  SI1441313

How do I do that in MS Access?

Upvotes: 0

Views: 63

Answers (2)

Parfait
Parfait

Reputation: 107567

To transpose to your needs requires a few nuances including numbering the times of repetition then use subqueries:

SomeQuery:

SELECT t1.IMPORT_ID, t1.EXPORT_ID 
   (SELECT COUNT(*) 
    FROM TableName AS t2
    WHERE t1.EXPORT_ID >= t2.EXPORT_ID 
     AND t1.IMPORT_ID = t2.EXPORT_ID) AS IDCount
FROM TableName AS t1;

TransposedQuery:

SELECT DISTINCT main.IMPORT_ID,
  (SELECT q1.EXPORT_ID FROM SomeQuery AS q1 
   WHERE q1.IMPORT_ID = main.IMPORT_ID AND q1.IDCount= 1) AS EXPORT1,
  (SELECT q2.EXPORT_ID FROM SomeQuery AS q2 
   WHERE q2.IMPORT_ID = main.IMPORT_ID AND q2.IDCount= 2) AS EXPORT2,
  (SELECT q3.EXPORT_ID FROM SomeQuery AS q3 
   WHERE q3.IMPORT_ID = main.IMPORT_ID AND q3.IDCount= 3) AS EXPORT3
FROM SomeQuery AS main;

Upvotes: 1

Abecee
Abecee

Reputation: 2393

Transposing with no support for it, and no analytic function either is probably hard to do. Not sure, why you actually need it transposed in the first place. In order to identify the problematic records, you might be able to work along:

CREATE VIEW DuplicateImportID AS
  SELECT
    import_id
  FROM SomeTable
  GROUP BY import_id
  HAVING COUNT(export_id) > 1
;

CREATE VIEW ExportIDForDuplicateImportID AS
  SELECT
    D.import_id
    , S.export_id
  FROM DuplicateImportID D
  JOIN SomeTable S
    ON D.import_id = S.import_id
;

SELECT
  *
FROM ExportIDForDuplicateImportID
ORDER BY import_id, export_id
;

Sorry - I have no MS Access at hand to verify / get any closer.
See it in action: SQL Fiddle

If you really need it transposed and are stuck with MS Access, you might want to look into:

CREATE VIEW DuplicateImportID AS
  SELECT
    import_id
  FROM SomeTable
  GROUP BY import_id
  HAVING COUNT(export_id) > 1
;

CREATE VIEW ExportIDForDuplicateImportID AS
  SELECT
    D.import_id
    , S.export_id
  FROM DuplicateImportID D
  JOIN SomeTable S
    ON D.import_id = S.import_id
;

CREATE VIEW ExportIDForDuplicateImportID1 AS
  SELECT
    import_id
    , MIN(export_id) export_id
    , 1 level
  FROM ExportIDForDuplicateImportID
  GROUP BY import_id
;

CREATE VIEW ExportIDForDuplicateImportID2 AS
  SELECT
    E.import_id
    , MIN(E.export_id) export_id
    , 2 level
  FROM ExportIDForDuplicateImportID E
  LEFT JOIN ExportIDForDuplicateImportID1 E1
    ON E.import_id = E1.import_id
    AND E.export_id = E1.export_id
  WHERE E1.export_id IS NULL
  GROUP BY E.import_id
;

CREATE VIEW ExportIDForDuplicateImportID3 AS
  SELECT
    E.import_id
    , MIN(E.export_id) export_id
    , 3 level
  FROM ExportIDForDuplicateImportID E
  LEFT JOIN ExportIDForDuplicateImportID1 E1
    ON E.import_id = E1.import_id
    AND E.export_id = E1.export_id
  LEFT JOIN ExportIDForDuplicateImportID2 E2
    ON E.import_id = E2.import_id
    AND E.export_id = E2.export_id
  WHERE E1.export_id IS NULL
    AND E2.export_id IS NULL
  GROUP BY E.import_id
;

CREATE VIEW Transposed AS
  SELECT
    D.import_id
    , (SELECT export_id FROM ExportIDForDuplicateImportID1 WHERE import_id = D.import_id) export_id1
    , (SELECT export_id FROM ExportIDForDuplicateImportID2 WHERE import_id = D.import_id) export_id2
    , (SELECT export_id FROM ExportIDForDuplicateImportID3 WHERE import_id = D.import_id) export_id3
  FROM DuplicateImportID D
;

as exemplified in this SQL Fiddle.
Not sure, MS Access will support this - MySQL does: SQL Fiddle.

Please comment, if and as this requires adjustment / further detail.

Upvotes: 1

Related Questions