sql server - MS SQL query to pivot in same column with multiple values -

sql server - MS SQL query to pivot in same column with multiple values -

table1 has list of persons have brought products (a,b,c)

i want show how many person has records each combination of products, using sql query.

please note:

1.the products not known , hence cannot hardcode product name, there can 'n' number of product names in table.

table1:

id person product ------------------- 1 jannet 2 jannet b 3 ross 4 jane c 5 jane b 6 jane 7 jannet

output required:

product b c ---------------------- 0 2 1 b 2 0 1 c 1 1 0

try this:

select product , sum(case when productx = 'a' 1 else 0 end) , sum(case when productx = 'b' 1 else 0 end) b , sum(case when productx = 'c' 1 else 0 end) c ( select distinct tb.product product , tb2.product productx , tb3.person ( select distinct product remsys.table_1 ) tb cross bring together ( select distinct product remsys.table_1 ) tb2 inner bring together remsys.table_1 tb3 on tb.product = tb3.product inner bring together remsys.table_1 tb4 on tb2.product = tb4.product tb.product <> tb2.product , tb3.person = tb4.person ) otb grouping product

sql sql-server tsql pivot multiple-columns