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

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:

CodePudding user response：

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

CodePudding user response：

-- create a item table
CREATE TABLE item (
  item_value integer NOT NULL,
  item_name TEXT NOT NULL
);

-- insert some values into item table
INSERT INTO item 
VALUES 
(1, 'A'),
(2, 'B'),
(4, 'C'),
(8, 'D'),
(16, 'E'),
(32, 'F'),
(64, 'G'),
(128, 'H'),
(256, 'I');

-- create a purchase table
CREATE TABLE purchase (
    item integer not null
);

-- insert some values into purchase table
-- you will be finding out the items correspond to each of these
INSERT INTO purchase
VALUES
(3),
(5),
(6),
(9),
(10),
(12),
(17),
(18),
(20);

-- your sql goes like this , i tested it on https://www.mycompiler.io/new/sql
select p.item, ref.first_item_name, ref.second_item_name
from purchase p
left join 
(
    select item, first_item_name, second_item_name
    from (
        select *, rank() over(partition by item order by first_item_name, second_item_name) r
        from (
            select i1.item_value   i2.item_value as item, i1.item_name as first_item_name, i2.item_name as second_item_name
            from item i1
            left join 
            item i2
            on 1 = 1
            order by item
        )
    )
    where r = 1
) ref
on p.item = ref.item

CodePudding user response：

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.