Finding the number of same products bought in different states in comparison to a particular state

Hi All,

Hope you are doing well. I am trying to finding the number of same products bought in different states in comparison to a particular state. I need to repeat this for all the states in which at least one product has been bought. For example if products ez12,ty234, Kl213 has been bought in michigan then I need to check the other states such as ohio, newyork and so on with the number of products among the three (ez12,ty234, Kl213) that has been bought in Michigan... I need to do the same thing for all the states keeping one state constant and comparing the products bought in that state to all the other states..


create table ##input

(productid varchar(20),

statebought varchar(30),

purchaseday date)

insert into ##input values












('primaryStatebought ','numberofdistinctproducts','michigan','ohio','newyork','maine','illinois','connecticut','delaware'),








Can you please help me here.. Please find below the snapshot of the input and output tables.



Since this is a pivot of data, and a dynamic one at that, is there any reason you can't do this in Excel or a reporting tool? It's going to be frustrating to do this directly in SQL Server.

Hi @robert_volk : Thanks for getting back to me!...The reason that I am trying to do it in SQL is that the table has like millions of rows of data..

What if you reduced it via this query:

SELECT a.statebought a_state, a.productid, b.statebought b_state 
FROM ##input a 
INNER JOIN ##input b ON a.productid=b.productid and a.statebought<>b.statebought 
GROUP BY a.statebought, a.productid, b.statebought

You're only looking for distinct counts on a_state and productID, and then pivoting the count of rows for a_state and columns for b_state.

Well, this seems to work, but it's pretty ugly:

DECLARE @sql nvarchar(max)=N'', @pivotcols nvarchar(4000)=N'';
SELECT @pivotcols+=N','+QUOTENAME(statebought) FROM (SELECT DISTINCT statebought FROM ##input) z
SELECT @pivotcols=stuff(@pivotcols,1,1,N'')
SELECT @sql+=N';with _0 AS (select a.statebought a_state, a.productid, b.statebought b_state from ##input a inner join ##input b on a.productid=b.productid and a.statebought<>b.statebought)
,_1(primaryStatebought,products) as (select a_state, count(distinct productid) products from _0 group by a_state)
select b_state primaryStatebought,(SELECT products FROM _1 WHERE primaryStatebought=b_state) products, ' + @pivotcols + N'
FROM (select a_state, b_state from _0) z
PIVOT(count(a_state) FOR a_state in(' + @pivotcols + N')) b
order by products desc'