text: Q:
  
  Using Case When to Split 1 Column Into Many While Ignoring Null Values
  
  I am fairly new to SQL and have tried many searches but cannot accomplish what I want.
  I have custom field values that are contained in one table and column, each custom field gets its own id, but the stored value is in one column.
  I am trying to get 1 column for each custom field with its associated value, here is my query:
  SELECT PRODUCT.id AS ProductId, PRODUCT.num AS ProductNumber, 
  CASE WHEN customvarcharlong.customfieldid = '6' THEN customvarcharlong.info 
  END AS OEM1Name, 
  CASE WHEN customvarcharlong.customfieldid = '7' THEN customvarcharlong.info 
  END AS OEM1NumbersA
  FROM product
  JOIN customvarcharlong ON product.id=customvarcharlong.recordid
  JOIN customfield ON customvarcharlong.customfieldid=customfield.id
  WHERE PRODUCT.activeFlag = TRUE
  ORDER BY PRODUCT.id
  
  My problem is that I get a row for every custom field that has a value, and the case statement simply returns NULL for each that does not match the customfieldid.
  2   00007   NULL    NULL
  2   00007   NULL    NULL
  2   00007   NULL    NULL
  2   00007   NULL    NULL
  2   00007   NULL    NULL
  2   00007   Comp1   NULL
  2   00007   NULL    1234
  2   00007   NULL    NULL
  2   00007   NULL    NULL
  
  Here is my desired result:
  2   00007   Comp1   1234
  
  Using where or group by eliminates some of the data since it is on different rows.  Is this possible?  I have 48 custom fields to add so you can imagine getting it right will save a lot of work.
  Thanks!
  Edit to add sample data from customvarcharlong:
  +--------+---------------+-------+----------+
  |   id   | customFieldId | info  | recordId |
  +--------+---------------+-------+----------+
  | 170887 |           171 | N     |     1481 |
  | 170888 |           172 | 3.03  |     1481 |
  | 170889 |           173 | new   |     1481 |
  | 170890 |           170 | 0     |     1481 |
  | 170891 |             5 | 0     |     1481 |
  | 170892 |             6 | OEM1  |     1481 |
  | 170893 |             7 | Num1  |     1481 |
  | 170894 |           200 | 4184  |     1481 |
  | 170895 |           120 | Truck |     1481 |
  +--------+---------------+-------+----------+
  
  As you can probably tell the issue is the recordid repeats for as many custom field values exist, and the customfieldid repeats on each recordid.  I would like to get the info value on each row in it's own column for each recordid (recordid=productid).
  
  A:
  
  You're working with an EAV data model. Essentially, you want a separate left join to the values table (customvarcharlong) for every attribute (customfieldid) you want to return for a given set of entities (product).
  Try it this way:
  SELECT PRODUCT.id AS ProductId, 
         PRODUCT.num AS ProductNumber, 
         cvcl6.info AS OEM1Name, 
         cvcl7.info AS OEM1NumbersA
  FROM product
      LEFT JOIN customvarcharlong cvcl6
          ON product.id=cvcl6.recordid
              AND cvcl6.customfieldid = '6'
      LEFT JOIN customvarcharlong cvcl7
          ON product.id=cvcl7.recordid
              AND cvcl7.customfieldid = '7'
  WHERE PRODUCT.activeFlag = TRUE
  ORDER BY PRODUCT.id
  
  