Need to create an SQL result suitable for data mining

Question

I have a database of supermarket transactions that I wish to use for associative data mining purposes. Below is an image of my current SQL query however I am not sure if what I require is possible with SQL (Explained below image).

I need my results to be in the following format - Each unique ProductName needs to have its own column with a binary 0/1 representation of whether the CustomerID purchased the product.

Is this possible? If so where exactly would I start?

Looking at the maximum limits you can have 1024 regular columns per table or 30000 if you use the sparse column feature but then you would use NULL not 0 for not purchased. Given the number of products in my local supermarket this doesn't feel like a very good fit.
https://learn.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver15#:~:text=Database%20Engine%20objects%20%20%20%20SQL%20Server,a%20cluster%20...%20%2029%20more%20rows%20 — Stephen Morris - Mo64, Mar 31 '21 at 08:35
@StephenMorris While important and good to be aware of, that's not really a relevant limitation in OP's scenario. He just needs to take his two column result set and PIVOT it into another result set (which would have no limitation on columns). The PIVOT query can be saved as a View (which he may then run into such a limitation), but it doesn't have to be. And we have no context on how he plans to consume this, e.g. if it's being pulled into Excel, then he's still in the clear as far as that column limitation goes. — J.D., Mar 31 '21 at 12:41
Not strictly true as from the link above Columns per SELECT statement = 4096 but I take your point. So something like https://kodyaz.com/articles/t-sql-dynamic-pivot-table-example-code.aspx ? — Stephen Morris - Mo64, Mar 31 '21 at 12:51
Does this answer your question? Pivot rows into multiple columns — , Mar 31 '21 at 14:51

Need to create an SQL result suitable for data mining

0 Answers0