CODEWITHSUNDEEP
sql-servert-sql
Brayn
Brayn

Reputation: 416

Crunching multiple rows into one per key

I am retrieving multiple features of a product and would like to display them in a single row. Here's what I am currently retrieving:

key     waterproof     oilfree     parabenfree
183     1              null        null
766     null           0           null
766     null           null        0
991     null           0           null 
991     null           null        0

A product may have zero or more features. My goal is to retrieve them like this:

key     waterproof     oilfree     parabenfree
183     1              null        null
766     null           0           0
991     null           0           0

The queried table looks something like the one below where the qualifier defines the type of feature.

key    qualifier      value
171    waterproof     1
766    oilfree        0
766    parabenfree    1
991    parabenfree    0    
991    oilfree        0

In a CASE WHEN I am creating a column for each feature. When the condition is met, I would like to see the value for that product. However, a feature is defined per row meaning that multiple rows can be retrieved for a product.

SELECT 
    CASE WHEN f.qualifier LIKE '%waterproof%'
        THEN f.value END as waterproof,
        
    CASE WHEN f.qualifier LIKE '%parabenfree%'
        THEN f.value END as parabenfree,
        
    CASE WHEN f.qualifier LIKE '%oilfree%'
        THEN f.value END as oilfree
FROM 
    features f

Are we able to crunch these rows into one per product while keeping the desired structure?

Here's an reproducible example:

-- create table
CREATE TABLE features (
  key INTEGER,
  qualifier TEXT,
  value INTEGER
);
-- insert data
INSERT INTO features VALUES (183, 'waterproof', 1),
                            (766, 'oilfree', 0),
                            (766, 'oilfree', 1),
                            (991, 'parabenfree', 0),
                            (991, 'parabenfree', 1);

-- statement
SELECT 
    f.key,
    CASE WHEN f.qualifier LIKE '%waterproof%'
        THEN f.value END as waterproof,

    CASE WHEN f.qualifier LIKE '%parabenfree%'
        THEN f.value END as parabenfree,

    CASE WHEN f.qualifier LIKE '%oilfree%'
        THEN f.value END as oilfree
FROM 
    features f

online version: https://www.mycompiler.io/view/83oxmgk

Upvotes: 1

Views: 84

Answers (1)

Stu
Stu

Reputation: 32599

You can aggregate the results using max() to eliminate null values, for example:

select f.[key],
    Max(case when f.qualifier like '%waterproof%' then f.value end  ) as waterproof,
    Max(case when f.qualifier like '%parabenfree%' then f.value end ) as parabenfree,
    Max(case when f.qualifier like '%oilfree%' then f.value end     ) as oilfree 
from features f
group by f.[key];

Upvotes: 7

Related Questions