oracle database - SQL generate columns using rows data from other table - Stack Overflow

admin管理员组

文章数量:1334887

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
  

• sql
• oracle-database

Share Improve this question Follow edited Nov 20, 2024 at 21:47 Barbaros Özhan 65.4k1111 gold badges3636 silver badges6161 bronze badges asked Nov 20, 2024 at 21:26 A SarafA Saraf 27577 silver badges2121 bronze badges 0

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 2

What 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

本文标签： oracle databaseSQL generate columns using rows data from other tableStack Overflow