SQLite: Trying to concatenate columns in more elegant way than string of UNIONs

Question

I have a table with the following fields and example value:

coursecode  BIO 101
a_code      FA
b_code      SP
c_code      SU
d_code
e_code

I'm trying to populate a field in another table (using coursecode as the key) with a comma separated list of all the "code" fields that have values (i.e. "FA, SP, SU"). I am accomplishing this successfully with the following code, but it is messy and was wondering if there was a more elegant way to accomplish this (esp. without all the UNION statements):

SELECT coursecode, group_concat(codes)
FROM (
SELECT 
    coursecode, 
    a_code AS codes
FROM scbsupp
    WHERE coursecode="BIO 317"
UNION
SELECT coursecode,
    b_code AS codes
FROM scbsupp
    WHERE coursecode="BIO 317" 
UNION
SELECT 
    coursecode, 
    c_code AS codes
FROM scbsupp
    WHERE coursecode="BIO 317"
UNION
SELECT coursecode,
    d_code AS codes
FROM scbsupp
    WHERE coursecode="BIO 317"
UNION
SELECT coursecode,
    e_code AS codes 
FROM scbsupp
    WHERE coursecode="BIO 317"
) WHERE codes != "";

For my own knowledge I would like to know if there's a better way to do this, I appreciate any help!

score 0 · Answer 1 · answered Jun 24 '14 at 07:57

You can do:

SELECT coursecode,
      LTRIM(
              CASE WHEN (a_code IS NOT NULL AND a_code <> '') THEN        a_code ELSE '' END ||
              CASE WHEN (b_code is NOT NULL AND b_code <> '') THEN ',' || b_code ELSE '' END ||
              CASE WHEN (c_code IS NOT NULL AND c_code <> '') THEN ',' || c_code ELSE '' END ||
              CASE WHEN (d_code IS NOT NULL AND d_code <> '') THEN ',' || d_code ELSE '' END ||
              CASE WHEN (e_code IS NOT NULL AND e_code <> '') THEN ',' || e_code ELSE '' END, ','
            )   AS code
  FROM scbsupp
    WHERE coursecode="BIO 317"

;

SQLite: Trying to concatenate columns in more elegant way than string of UNIONs

1 Answers1