0

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).

Current SQL Result

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.

Required format

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

Improve this question
5
  • 1
    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. learn.microsoft.com/en-us/sql/sql-server/…
    – Stephen Morris - Mo64
    Mar 31, 2021 at 8: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, 2021 at 12:41
  • Not strictly true as from the link above Columns per SELECT statement = 4096 but I take your point. So something like kodyaz.com/articles/t-sql-dynamic-pivot-table-example-code.aspx ?
    – Stephen Morris - Mo64
    Mar 31, 2021 at 12:51
  • 5
    Does this answer your question? Pivot rows into multiple columns
    – user212533
    Mar 31, 2021 at 14:51
  • 1
    Thankyou @StephenMorris-Mo64, this article has helped
    – Owen
    Mar 31, 2021 at 20:11

0

Reset to default

Browse other questions tagged or ask your own question.