SQL - How to have query auto calculate possibilities based on Case When type framework?

Question

I know the title is a little vague, but I have a situation where the query I am writing uses a CASE WHEN statement to identify categories selected on the front end. The table (Purchased) I am querying has a column named item. The item table of course has the unique identifier for each item. The item column in the Purchased table will show the number of the item purchased, but if multiple items were purchased, then the numbers are sum together. An example would be:

ITEM  TABLE
-------------
1     Item A
2     Item B
4     Item C
8     Item D

and so on where the max Item unique identifier is 256 (1,2,4,8,16,32,64,128,256). A doubling sequence was used so that every number in the Purchased table can only have one possible item or groups of items associated with it.

So in the Purchased table, if in the item column there is a '3', then both Item A and Item B were purchased. I can do a CASE WHEN to show the Item name that was purchased if only 1 item was purchased, but looking for a way to write a query where I designate what each number is equal to as an Item name and then the query takes the '3' and then CONCAT the Items that match that number.

Otherwise, I would have a very long Case When statement and I would have hundreds on possibilities.

Hope this makes sense. Relatively new to SQL. Thank you in advance.

Tables to Start

Item Table

Sales Table

I would normally perform a:

CASE WHEN s.ItemsPurchased = 1 THEN 'Bike' WHEN s.ItemsPurchased = 2 THEN 'Helmet' .... END as ItemsPurchased,

The issue is the multiple items in one order and the many, many possibilities within the structure.

I would like the end result to look something like this:

Sorry, I mispoke before. The Item number (i.e. 1,2,4,8,16....) is not the unique identifier but is from a column named "Value". "Value" is what I am using from the Item table to perform the CASE When originally. — Buckets, Feb 21 '22 at 17:42
This is called a [bitmask](https://stackoverflow.com/a/31576303/2221001). Please share the RDBMS you are using (postgres, mysql, sql server, oracle, teradata, snowflake, etc) and it's likely there are special bit-manipulation functions that will make quick work of this. — JNevill, Feb 21 '22 at 17:48
Without knowing what drove this particular design pattern but it's not going to be good for performance, You'll need to use a bitmask to extract your values. It would be much better imho to properly normalise the data and store Item Ids as rows with a foreign key with a unique constraint, you then do not have any issues. What happens in the future when you need more items? — Stu, Feb 21 '22 at 17:48
[Why should I "tag my RDBMS"?](https://meta.stackoverflow.com/questions/388759/why-should-i-tag-my-rdbms) - please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. — marc_s, Feb 21 '22 at 17:53
The database is one that was built by a company that we use for our data entry and then we have access to the backend for our web application. Believe me, I am frustrated with how they designed certain aspects of the database. — Buckets, Feb 21 '22 at 17:59
There is nothing in your question that tells us what your expectations are. — Stu, Feb 21 '22 at 18:03
@Stu My expectations are wanting to know if it is possible to do what I have mentioned in my original post (relatively new to SQL and still learning all of the possibilities of SQL) and then if so, how do I go about doing that. — Buckets, Feb 21 '22 at 18:09
Most would expect sample data and desired results, as explained in the [question guide](https://stackoverflow.com/help/how-to-ask) and how to provide a [Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example) — Stu, Feb 21 '22 at 18:12
@Stu Let me look into building out some fake data. I work in a field that is heavily regulated and has very sensitive data, so I have already sanitized it a lot. — Buckets, Feb 21 '22 at 18:17
Fake data is all you need, we need to see what you have and, cruicially, what you expect the output to look like. — Stu, Feb 21 '22 at 18:22
T-SQL fortunately does have bitwise operators, so you can use the peculiar construct `CROSS JOIN Products ON Purchased.Product & Products.ID <> 0` to do bitwise filtering. — Jeroen Mostert, Feb 21 '22 at 18:31
@Stu I edited my original post to include some dummy data and what the end result would need to look like — Buckets, Feb 21 '22 at 20:31
So, it's impossible to buy two helmets, or have a thousand different products, ever? What an awful design (for many reasons), you have my sympathies. — MatBailie, Feb 21 '22 at 21:00
@MatBailie Thanks, The design of the database makes it a struggle sometimes, but the data is fake in this situation. In the real situation and what all the "items" are, it is impossible to have 2 of the same selected. — Buckets, Feb 21 '22 at 21:04

Donat · Answer 1 · 2022-02-21T22:29:40.803

1

I have used this test data:

create table purchase(pid integer, items integer);
create table items(item integer, name varchar(12));

insert into purchase (pid, items) values (4711, 3);
insert into purchase (pid, items) values (4712, 13);
insert into items(item, name) values (1, 'Item A');
insert into items(item, name) values (2, 'Item B');
insert into items(item, name) values (4, 'Item C');
insert into items(item, name) values (8, 'Item D');

Then I can do a query like this:

select p.pid, string_agg(i.name, ', ')
from purchase p
     inner join items i on p.items & i.item = i.item
group by p.pid;

You can easily adapt this example for your use case.

edited Feb 21 '22 at 22:29

answered Feb 21 '22 at 18:58

Donat

4,157
3
11
26

1

Why not just use bitwise AND; `&`? – MatBailie Feb 21 '22 at 21:03
@MatBailie thanks for the hint, this is a little bit simpler. I do not know MS SQL Server very well, only other databases. So I first chose the other solution. – Donat Feb 21 '22 at 22:19

score 1 · Answer 2 · answered Feb 21 '22 at 20:54

If I understand correctly you can do something like the following, using string_agg (SQL Server 2017+)

select s.CustomerId, i.ItemsPurchased
from Sales s
cross apply (
    select String_Agg(i.ItemName,', ') ItemsPurchased
    from Item i
  where i.Id & s.Item != 0
)i

See Example Fiddle

Sample results:

SQL - How to have query auto calculate possibilities based on Case When type framework?

2 Answers2