Lets assume we have this cvs data (scroll below). It shows customerID, ProductID and the time the product was purchased. We are required to figure out which product customer bought right after a purchase . We want the output in this form.
We are most likely going to use SQL PIVOT but the output form cannot be directly obtained by simply PIVOTING the data. The reason being each product will have it own column in the pivot table which we do not want (will be too much to digest the data). Here is the query that will do the trick. It uses PIVOT but it also uses row_number() and assigned a psuedo prod names to each product (prod1, prod2, prod3,… and so on) which simplies the pivoting
select * from ( select Customer, 'Prod' + cast( row_number() OVER (partition by customer order by time) as varchar(2)) as ProductName, Product from transactions ) A PIVOT ( sum(product) for ProductName in ( [Prod1],[Prod2],[Prod3],[Prod4],[Prod5],[Prod6]) ) B
The result output is this
CVS Data to play with
You can import this into SQL Server using the import feature in SSMS. Make sure you change the product column to int type after import (by default it will be stored in varchar)