Concatenate string using +=

Question

I am trying to concatenate some strings in sql. What I am trying to do is something like

string organType = null;
if (liver!=null)  { organType += "LI, "; }
if (kidney !=null) { organType += "KI, "; }
if (intestine != null) { organType += "Intestine"; } 
...

The end result should be organType = LI, KI, Intestine;

Here is my code so far

create or replace PROCEDURE "insertDonInfo"(donNum IN NUMBER, offerDate IN DATE)

IS

organType varchar2(100);
BEGIN

  select case when liver is not null then 'LI' 
              when kidney_r is not null then 'KR'
              when kidney_l is not null then 'KL' 
              when heart is not null then 'HE'
              when liver_domino is not null then 'LI-Dom'
              when lung_r is not null then 'LungR'
              when pancreas is not null then 'PA'
              when liver_split is not null then 'Lsplit'
              when lung_l is not null then 'LungL'
              when intestine is not null then 'Intestine' 
         end                         
from donors
where id = donNum;

...

-------------------------Update---------------------

How do I concatenate the organType to be organType=LI, KR, KL, HE, ... in SQL;

Answer 1

sql does not have a += operator. You'll have to check on a per column basis and concatenate. Took a stab at your data structure.

create table so_test (id number primary key, don_name varchar2(100), liver varchar2(1), heart varchar2(1), kidney_r varchar2(1));

insert into so_test (id, don_name, liver, heart, kidney_r) values (1, 'John','Y',NULL,'Y');
insert into so_test (id, don_name, liver, heart, kidney_r) values (2, 'Kathy',NULL,'Y','Y');

SELECT 
  don_name,
  RTRIM(
    CASE WHEN liver IS NOT NULL THEN 'LI, ' ELSE NULL END ||
    CASE WHEN heart IS NOT NULL THEN 'HE, ' ELSE NULL END ||
    CASE WHEN kidney_r IS NOT NULL THEN 'KR, ' ELSE NULL END
  ,', ') as organs
  FROM so_test;

returns

John    LI, KR
Kathy   HE, KR