Using Case When to Split 1 Column Into Many While Ignoring Null Values

Question

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).

Can you post a sample of your data? With all of those fields? — Alexandre Juma, Dec 05 '18 at 18:58
wrap each case in a max() statement and group by the other fields. This assumes there can be only one allowed value in each column; or you will get the "Max" :P — xQbert, Dec 05 '18 at 20:44
@xQbert unfortunately not that simple since all values are in the same column, so I just get the last result alphabetically or numerically for each customfieldid in each case. — Reuben Bakker, Dec 05 '18 at 21:02
I'm getting a Xy problem feeling here... This appears to be a dynamic pivot based on the CustomFieldID. https://stackoverflow.com/questions/12598120/mysql-pivot-table-query-with-dynamic-columns is this what you're after? If you could setup a rextester.com table w/ examples and show expected output I'm sure we can figure this out. — xQbert, Dec 05 '18 at 22:05

Joe Stefanelli · Accepted Answer · 2018-12-05T22:27:34.677

1

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

edited Dec 05 '18 at 22:27

answered Dec 05 '18 at 22:15

Joe Stefanelli

132,803
19
237
235

Excellent solution, worked great! And thank you for the link, I learned something new! – Reuben Bakker Dec 06 '18 at 20:17
@ReubenBakker Glad to hear it! Please remember to accept the answers that help you. https://stackoverflow.com/help/someone-answers – Joe Stefanelli Dec 06 '18 at 22:56

Using Case When to Split 1 Column Into Many While Ignoring Null Values

1 Answers1