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

Transact-SQL
1

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

##Input

create table ##input

(productid varchar(20),

statebought varchar(30),

purchaseday date)

insert into ##input values

('ez12','Michigan','2/1/2022'),

('ez12','Ohio','2/22/2022'),

('ez12','newyork','2/14/2022'),

('ty234','Michigan','3/4/2022'),

('ty234','maine','3/25/2022'),

('ty234','Illinois','4/25/2022'),

('ty234','Ohio','5/25/2022'),

('Kl213','Michigan','6/20/2022'),

('Kl213','connecticut','9/21/2022'),

('Kl213','delaware','10/20/2022')

##output

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

('Michigan','3','','2','1','1','1','1','1'),

('Ohio','2','2','','1','1','1','',''),

('newyork','1','1','1','','','','',''),

('Maine','1','1','1','','','1','',''),

('Illinois','1','1','1','','1','','',''),

('connecticut','1','1','','','','','','1'),

('delaware','1','1','','','','','1',''),

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

input
input870×282 8.39 KB

output
output968×201 5.48 KB

Thanks,

Arun

2

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.

3

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

4

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.

5

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'
EXEC(@sql);