0

I have 3 tables - Customer Info (Customer_ID, Name, State, Income), Product Info (Product_ID, Product_Name, Price) & Sales Info (Customer_ID, Purchase_Date, Quantity, Store_ID).

I am trying to list every customer (Customer_Info.Name) and count for the products they bought. The columns are all the products available for sale. I can do a count with a select for each of the product ID manually but I'm trying to avoid that.

The output will look something like

Name  |    Product1   |  Product2  |   Product3  |   Product4 .......
AAA.  |       1.      |    NULL.   |       2.    |      1.    .......

....

JNevill
  • 46,980
  • 4
  • 38
  • 63
user11996382
  • 1
  • 1
    Possible duplicate of [MySQL pivot row into dynamic number of columns](https://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns) The answer there shows how to do this with case statements where the products are known and can be encoded into the SQL as well as how to dynamically pivot which is a bit more complicated. Interestingly they are also listing out products in the columns with counts :) – JNevill Aug 29 '19 at 21:02
  • Thanks JNevill. It answers my question. I couldn't find this when I tried to search earlier. – user11996382 Aug 29 '19 at 21:10
  • No worries. Sometimes it's hard to search when you don't know all the right terms to search for. – JNevill Aug 29 '19 at 21:12

0 Answers0