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:

Answer 1

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:

Answer 2

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.