CODEWITHSUNDEEP
sqlms-accessms-access-2010
Shawn Koh
Shawn Koh

Reputation: 137

How to combine two rows of data from the same table?

I have a table called document as follow:

document_table

Sample data as below.

doc_id   employee_id   type         status
--------------------------------------------------
1        S1234         transcript   ready to print
2        S1234         testimonial  ready to print
3        S2345         transcript   ready to print

I want result table to be like below.

Result_table:

I would like to write a sql query to combine the individual document records into one, based on employee_id, and based on the document type being either transcript or testimonial

doc_id  transcript     testimonial
--------------------------------
S1234   TRUE           TRUE
S2345   TRUE           FALSE

I'm using MS Access 2010. How do I achieve this?

Upvotes: 0

Views: 2956

Answers (2)

June7
June7

Reputation: 21370

Simplest approach, although will only return TRUE where there is data, FALSE will just be empty field: 

TRANSFORM First("TRUE") AS S
SELECT document.employee_id
FROM document
GROUP BY document.employee_id
PIVOT document.type In ("testimonial","transcript");

Don't think Access SQL recognizes the CASE structure, so alternate version of zarruq answer: 

SELECT
  employee_id,
  IIf(Max(transcript)="transcript","TRUE","FALSE") AS tran, 
  IIf(Max(testimonial)="testimonial", "TRUE","FALSE") AS test
FROM (SELECT
  employee_id,
  type AS transcript,
  '' AS testimonial
FROM document
WHERE type = 'transcript'

UNION ALL

SELECT
  employee_id,
  '' AS transcript,
  type AS testimonial
FROM document
WHERE type = 'testimonial')
GROUP BY employee_id;

Upvotes: 1

zarruq
zarruq

Reputation: 2465

Using SQL, One approach could be to separate transcript and testimonial data in separate columns using union in inner query and then use max and case in outer query as below to get your desired result as below.

SELECT
  employee_id,
  CASE
    WHEN MAX(transcript) = 'transcript' THEN 'TRUE'
    ELSE 'FALSE'
  END AS transcript,
  CASE
    WHEN MAX(testimonial) = 'testimonial' THEN 'TRUE'
    ELSE 'FALSE'
  END AS testimonial
FROM (SELECT
  employee_id,
  type AS transcript,
  '' AS testimonial
FROM t1
WHERE type = 'transcript'

UNION ALL

SELECT
  employee_id,
  '' AS transcript,
  type AS testimonial
FROM t1
WHERE type = 'testimonial') t
GROUP BY employee_id;

Result:

employee_id    transcript   testimonial
---------------------------------------
S1234          TRUE         TRUE
S2345          TRUE         FALSE

You can check the demo here

Upvotes: 0

Related Questions