I am trying to create a data set which shows what all tests are performed for a patient from a sql table as shown below. Add values from rows to new table columns.
Input data:
| patient_id | test_id | test_name |
| ---------- | -------------- | --------- |
| 010 | A11 | blood |
| ---------- | -------------- |-----------|
| 010 | A12 | stool |
| ---------- | -------------- |-----------|
| 010 | A13 | xray |
| ---------- | -------------- |-----------|
| 011 | A13 | xray |
| ---------- | -------------- |-----------|
| 012 | A12 | stool |
| ---------- | -------------- |-----------|
Expected result:
When test_id = A11 Then add value to column clinic_test
When test_id = A12 Then add value to column lab_test
When test_id = A12 Then add value to column radio_test
expected sample data:
| patient_id | clinic_test | lab_test | radio_test |
| ---------- |------------ | -------- | ---------- |
| 010 | blood | stool | xray |
| ---------- |------------ | -------- | ---------- |
| 011 | null | null | xray |
| ---------- |------------ | -------- | ---------- |
| 012 | null | stool | null |
| ---------- |------------ | -------- | ---------- |
Tried case statement but it is giving me 5 rows instead of 3 as expected above
(
CASE
WHEN test_id = A11 THEN test_name
ELSE NULL
END
) clinic_test ,
(
CASE
WHEN test_id = A12 THEN test_name
ELSE NULL
END
) lab_test,
(
CASE
WHEN test_id = A13 THEN test_name
ELSE NULL
END
) radio_test
I am trying to create a data set which shows what all tests are performed for a patient from a sql table as shown below. Add values from rows to new table columns.
Input data:
| patient_id | test_id | test_name |
| ---------- | -------------- | --------- |
| 010 | A11 | blood |
| ---------- | -------------- |-----------|
| 010 | A12 | stool |
| ---------- | -------------- |-----------|
| 010 | A13 | xray |
| ---------- | -------------- |-----------|
| 011 | A13 | xray |
| ---------- | -------------- |-----------|
| 012 | A12 | stool |
| ---------- | -------------- |-----------|
Expected result:
When test_id = A11 Then add value to column clinic_test
When test_id = A12 Then add value to column lab_test
When test_id = A12 Then add value to column radio_test
expected sample data:
| patient_id | clinic_test | lab_test | radio_test |
| ---------- |------------ | -------- | ---------- |
| 010 | blood | stool | xray |
| ---------- |------------ | -------- | ---------- |
| 011 | null | null | xray |
| ---------- |------------ | -------- | ---------- |
| 012 | null | stool | null |
| ---------- |------------ | -------- | ---------- |
Tried case statement but it is giving me 5 rows instead of 3 as expected above
(
CASE
WHEN test_id = A11 THEN test_name
ELSE NULL
END
) clinic_test ,
(
CASE
WHEN test_id = A12 THEN test_name
ELSE NULL
END
) lab_test,
(
CASE
WHEN test_id = A13 THEN test_name
ELSE NULL
END
) radio_test
Share
Improve this question
edited Nov 20, 2024 at 21:47
Barbaros Özhan
65.4k11 gold badges36 silver badges61 bronze badges
asked Nov 20, 2024 at 21:26
A SarafA Saraf
2757 silver badges21 bronze badges
0
2 Answers
Reset to default 2What you need is a conditional aggregation in order to return single row per each different patient_id
such as
SELECT patient_id,
MAX(
CASE
WHEN test_id = 'A11' THEN test_name
END
) AS clinic_test,
MAX(
CASE
WHEN test_id = 'A12' THEN test_name
END
) AS lab_test ,
MAX(
CASE
WHEN test_id = 'A13' THEN test_name
END
) AS radio_test
FROM t
GROUP BY patient_id
Here is the online Demo
You can use PIVOT
:
SELECT *
FROM data
PIVOT(
LISTAGG(test_name, ',')
FOR test_id IN (
'A11' AS clinic_test,
'A12' AS lab_test,
'A13' AS radio_test
)
)
Which, for the sample data:
CREATE TABLE data (patient_id, test_id, test_name) AS
SELECT '010', 'A11', 'blood' FROM DUAL UNION ALL
SELECT '010', 'A12', 'stool' FROM DUAL UNION ALL
SELECT '010', 'A13', 'xray' FROM DUAL UNION ALL
SELECT '011', 'A13', 'xray' FROM DUAL UNION ALL
SELECT '012', 'A12', 'stool' FROM DUAL;
Outputs:
PATIENT_ID | CLINIC_TEST | LAB_TEST | RADIO_TEST |
---|---|---|---|
010 | blood | stool | xray |
011 | null | null | xray |
012 | null | stool | null |
fiddle