0

I have data that looks like this example for 1 user - I only have 1 table:

ID     procedure  date
12345     2        2023-02-27
12345     3        2023-02-06
12345     4        2023-02-13
12345     5        2023-02-27
12345     8        2023-02-27

I need it to be in 1 row as, with data in separate columns :

12345 2 2023-02-27 3 2023-02-06 4 2023-02-13 5 2023-02-27 8 2023-02-27

The codes 2,3,4,5, & 8 will be replaced by text probably using a case statement.

I've tried grouping, joining, concatenating but nothing is giving me the desired result.

Using a case statement approach is the closest I've gotten but it gave the date from the first row for all date columns and only gave the the procedure correctly in 2 of the columns, the others it gave me a null.

user21220560
  • 1
  • 2
  • 1
    Use `GROUP_CONCAT(date SEPARATOR ' ')` – Barmar Feb 15 '23 at 15:54
  • These need to be in separate columns. I neglected to state that specifically. – user21220560 Feb 15 '23 at 16:41
  • Then what you want is a "pivot". I've provided a link to that. – Barmar Feb 15 '23 at 16:44
  • I looked at those, the case statements I mentioned earlier that got me closest are much like that but the wrong date comes in. Here is how I have it currently - why don't the correct dates come along with the procedure code: select donor_code ,case procedure_id when 1 then 'WB' End ProcedureCodeWB ,date as EligibilityDateWB ,case procedure_id when 3 then 'APH' End ProcedureCodePLT ,date as EligibilityDatePLT ,case procedure_id when 2 then 'DRBC' End ProcedureCodeDRBC ,date as EligibilityDateDRBC etc. from ..... – user21220560 Feb 15 '23 at 17:38
  • Please post your query in the question. – Barmar Feb 15 '23 at 17:48
  • You need an aggregation function, like shown in the linked question: `SELECT MAX(CASE ...)` – Barmar Feb 15 '23 at 17:48
  • Here is some of the query - the entire doesn't fit. Also I don't see a linked question for select Max(case) but I can search for it. select donor_code ,if(procedure_id = 1,'WB',null) as ProcedureCodeWB ,if(procedure_id = 1,date,null) as EligibilityDateWB ,if(procedure_id = 3,'APH',null) as ProcedureCodePLT ,if(procedure_id = 3,date,null) as EligibilityDatePLT ...more here but just different id numbers and aliases... from rrv.donor_eligibility inner join rrv.vw_donor_becs on becs_id = id where date > '2023-02-01' and becs_id = 20815 – user21220560 Feb 15 '23 at 18:50
  • Don't put code in comments, edit it into the question with code formatting. – Barmar Feb 15 '23 at 18:56
  • You still have no aggregation function. `MAX(CASE WHEN procedure_code = 1 THEN date END) AS date1, MAX(CASE WHEN procedure_code = 2 THEN date END) AS date2, ...` – Barmar Feb 15 '23 at 18:58
  • I found and tried Max(case) and it is working. Thank you – user21220560 Feb 15 '23 at 19:10

0 Answers0