find all stores customers have made purchase

Question

I have a table that contains all the transaction information

(transaction date, customer name, transaction amount, unit amount, store ID).

How to create a table which could properly show the stores that customer has visited? The customer could make purchase from multiple stores, so the relationship between customer and store should be one to many. I am trying to check the stores each customer had visited.

Example: Transaction table

store_ID
transaction_date
customer_name
transaction_amount
transaction_unit

Expected Output

customer_name
store_list

This is just a Hypothesis problem. Maybe list all shopped store seprately could be better? (But I guess it might create chaos if we want to check customer who made purchase in those stores). Thanks in advance :)

What have you tried so far? Also, it would help if you gave us an ER diagram of your current setup. — David Brossard, Feb 16 '21 at 18:37
Does this answer your question? [How to concatenate text from multiple rows into a single text string in SQL server?](https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv) — David Brossard, Feb 16 '21 at 18:40
Where is the code you tried? Where are you stuck right now? Can you provide sample data? — T.kowshik Yedida, Feb 16 '21 at 18:41

score 0 · Answer 1 · edited Mar 19 '21 at 20:55

Since you already have a table with all information needed, maybe you are asking for a view, to avoid denormalized/duplicated data.

Example is shown below. Here I will use sqlite syntax (text instead of varchar, etc). We are normalizing data to avoid problems - for example you have shown a table with customer names, but different customers can have the same name, so we use customer_id instead.

First the tables:

create table if not exists store (id integer primary key, name text, address text);

create table if not exists customer (id integer, name text, document integer);

create table if not exists unit (symbol text primary key);
insert into unit values ('USD'),('EUR'),('GBP'); -- static data

create table if not exists transact (id integer primary key, store_id integer references store(id), customer_id integer references customer(id), transaction_date date, amount integer, unit text references unit(symbol));

-- transaction is a reserved word, so transact was used instead

Now the view:

-- store names concatenated with ',' using sqlite's group_concat
create view if not exists stores_visited as select cust.name, group_concat(distinct store.name order by store.id) from transact trx join customer cust on trx.customer_id=cust.id join store on trx.store_id=store.id group by cust.name order by cust.id;

-- second version, regular select with multiple lines
-- create view if not exists stores_visited as select cust.id, cust.name, store.id, store.name from transact trx join customer cust on trx.customer_id=cust.id join store on trx.store_id=store.id;

Sample data:

insert into store values
  (1,'store one','address one'),
  (2,'store two','address two')
  (3,'store three','address three');

insert into customer values
  (1,'customer one','custdoc one'),
  (2,'customer two','custdoc two'),
  (3,'customer three','custdoc three');

insert into transact values
  (1,1,1,date('2021-01-01'),1,'USD'),
  (2,1,1,date('2021-01-02'),1,'USD'),
  (3,1,2,date('2021-01-03'),1,'USD'),
  (5,1,3,date('2021-01-04'),1,'USD'),
  (6,2,1,date('2021-01-05'),1,'USD'),
  (7,2,3,date('2021-01-06'),1,'USD'),
  (8,3,2,date('2021-01-07'),1,'USD');

Testing:

select * from stores_visited;

-- customer one|store one,store one,store two
-- customer three|store one,store two
-- customer two|store one

find all stores customers have made purchase

1 Answers1