CODEWITHSUNDEEP
sqlpostgresql
scott martin
scott martin

Reputation: 1293

Postgres - Searching for a particular value across columns in a table

I have a table that list of all fruits bought by a customer at a store by customer:

| cust_name | fruit1 | fruit2  | fruit3 |
|-----------|--------|---------|--------|
| cust_a    | apples | oranges | pears  |
| cust_b    | pears  |         |        |
| cust_c    |        |         | apples |

I am trying to create an output where shows which fruit (fruit1 / fruit2, fruit3) was bought by a customer that is tagged as apples from the above table. I know case statement can only be applied on a single column so I am wondering if there is a way to get customers who have bought apples.

Expected output :

cust_a,fruit1
cust_b,
cust_c,fruit3

Upvotes: 1

Views: 1105

Answers (3)

jferard
jferard

Reputation: 8180

You can create a type that stores a fruit and it's number:

CREATE TYPE num_fruit AS (
    num integer,
    fruit text
);

Given this type, you can use unnest to expand your columns into rows (I'm not sure that the right expression):

CREATE TABLE customer_fruits (cust_name text, fruit1 text, fruit2 text, fruit3 text);
INSERT INTO customer_fruits VALUES 
  ('cust_a', 'apples', 'oranges', 'pears'),
  ('cust_b', 'pears', NULL, NULL),
  ('cust_c', NULL, NULL, 'apples'),
  ('cust_d', 'apples', NULL, 'apples');


SELECT cust_name, unnest(ARRAY[ROW(1, fruit1), ROW(2, fruit2), ROW(3, fruit3)]::num_fruit[]) as nf FROM customer_fruits;

Output:

cust_name | nf
-----------------------
cust_a    | (1,apples)
cust_a    | (2,oranges)
cust_a    | (3,pears)
cust_b    | (1,pears)
cust_b    | (2,)
cust_b    | (3,)
cust_c    | (1,)
cust_c    | (2,)
cust_c    | (3,apples)
cust_d    | (1,apples)
cust_d    | (2,)
cust_d    | (3,apples)

Now, you just to SELECT rows on the fruit and return the num:

WITH t AS (
    SELECT cust_name, unnest(ARRAY[ROW(1, fruit1), ROW(2, fruit2), ROW(3, fruit3)]::num_fruit[]) as nf FROM customer_fruits
) SELECT cust_name, 'fruit' || num(nf) as 'fruit' FROM t WHERE fruit(nf) = 'apples';

cust_name | fruit
------------------
cust_a    | fruit1
cust_c    | fruit3
cust_d    | fruit1
cust_d    | fruit3

Or: 

WITH t AS (
    SELECT cust_name, unnest(ARRAY[ROW(1, fruit1), ROW(2, fruit2), ROW(3, fruit3)]::num_fruit[]) as nf FROM customer_fruits
) SELECT cust_name, array_agg('fruit' || num(nf)) as 'fruits' FROM t WHERE fruit(nf) = 'apples' GROUP BY 1;

cust_name | fruits
------------------
cust_c    | {fruit3}
cust_a    | {fruit1}
cust_d    | {fruit1, fruit3}

You can even normalize your table (PK = cust_name + num):

WITH t AS (
    SELECT cust_name, unnest(ARRAY[ROW(1, fruit1), ROW(2, fruit2), ROW(3, fruit3)]::num_fruit[]) as nf FROM the_table
) SELECT cust_name, num(nf), fruit(nf) FROM t WHERE fruit(nf) IS NOT NULL;

Upvotes: 0

user330315
user330315

Reputation:

One way to do this without writing a complicated WHERE clause and and that easily extends to more columns) is to convert the row into a JSON and iterate over the keys of the resulting JSON value:

select t.cust_name, string_agg(r.field, ',')
from the_table t
  left join lateral jsonb_each_text(to_jsonb(t) - 'cust_name') as r(field, fruit) 
                 on r.fruit = 'apples'
group by t.cust_name;

to_jsonb(t) - 'cust_name' create a JSON value with all columns from the row and removes the cust_name. It's not strictly necessary to remove cust_name from the JSON as it is unlikely it contains a fruit name, so it would never be returned anyway.

jsonb_each_text() then "iterates" over all columns and leaves only those that contain the value apples the result is then aggregated back into a comma separated list in case.

With the following sample data:

create table the_table (cust_name text, fruit1 text, fruit2 text, fruit3 text)
insert into the_table
values 
  ('cust_a', 'apples', 'oranges', 'pears'),
  ('cust_b', 'pears', null, null),
  ('cust_c', null,  null, 'apples'),
  ('cust_d', 'apples',  null, 'apples');

The above query returns:

cust_name | string_agg   
----------+--------------
cust_a    | fruit1       
cust_b    |              
cust_c    | fruit3       
cust_d    | fruit1,fruit3

Properly normalizing the data model would be a much better solution though.

Upvotes: 3

Arulkumar
Arulkumar

Reputation: 13237

Using CASE with multiple WHEN you can get the expected result:

DECLARE FruitName VARCHAR(50) := 'apples';

SELECT cust_name,
       CASE WHEN fruit1 = FruitName THEN 'fruit1'
            WHEN fruit2 = FruitName THEN 'fruit2'
            WHEN fruit3 = FruitName THEN 'fruit3'
       ELSE '' END AS fruit

Upvotes: 0

Related Questions