SQLTeam.com | Weblogs | Forums

Customer Purchasing Pattern


#1

Hi,

I am trying to find customer purchasing pattern from a table.

An order with 'Jacket & Trouser' is considered as 'Suit'

I need to count all orders with Suits and other additional items purchased.
(ignore all order which doesn't have suits i.e 'Jacket and trouser')

Table

OrderID OrderCount Product
1419253 1 Jackets
1419258 1 Jackets
1419258 1 Trousers
1419259 1 Trousers
1419259 1 Jackets
1419259 1 waistcoat
1419266 1 Tie
1419266 1 waistcoat
1419266 1 Jackets
1419266 1 Trousers

Expected Result

Suits Waistcoat Tie
3 2 1

Calculation for the above result
1419253 - ignore, no suit combination
1419258 - suit
1419259 - suit + waistcoat
1419266 - suit + waistcoat + tie

Note
There are 20+items can be selected as additional items when purchased, I just showed 'tie and waistcoat' for an example.

How to write a query to do this calculation. Any help will be greatly appreciated.

REgards,
SG


#2
create table #sgs(OrderID int, OrderCount int, Product varchar(50))

;with cte
as
(
select '1419253' as OrderID,	1 OrderCount	,'Jackets' Product union 
select '1419258',	1	,'Jackets' union 
select '1419258',	1	,'Trousers' union 
select '1419259',	1	,'Trousers' union 
select '1419259',	1	,'Jackets' union 
select '1419259',	1	,'waistcoat' union 
select '1419266',	1	,'Tie' union 
select '1419266',	1	,'waistcoat' union 
select '1419266',	1	,'Jackets' union 
select '1419266',	1	,'Trousers' 
)
insert into #sgs
select distinct OrderID,
       sum(OrderCount),
       case 
		when r.Product in ('Jackets','Trousers') then 'Suits'
		else r.Product 
		end as Product
from cte r
group by OrderID, Product

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX),
	@Piv as NVARCHAR(MAX)

SELECT  @cols = LEFT( b.Piv, LEN( b.Piv ) - 1 )
FROM (  SELECT  N'[' + a.Product  + '], '
        FROM (  SELECT  DISTINCT Product
                  FROM #sgs r ) a
        ORDER BY a.Product
        FOR XML PATH ( '' ) ) b ( Piv );

print @cols

set @query = 'SELECT ' + @cols + ' from 
            (
                select Product, 
				OrderCount
                from #sgs
           ) x
            pivot 
            (
                 count(OrderCount)
                for Product in (' + @cols + ')
            ) p '

select @query

SELECT [Suits], [waistcoat], [Tie] 
  from (                  
         select Product, 
		        OrderCount
		   from #sgs) x              
		   pivot ( count(OrderCount) for Product in ([Suits], [Tie], [waistcoat])) p 

execute(@query)



drop table #sgs

#3

Thank You yasiaz. It's working like a charm. But I am not able to understand your query.

I would appreciate if you could explain the below, please?

SELECT @cols = LEFT( b.Piv, LEN( b.Piv ) - 1 )
FROM ( SELECT N'[' + a.Product + '], '
FROM ( SELECT DISTINCT Product
FROM #sgs r ) a
ORDER BY a.Product
FOR XML PATH ( '' ) ) b ( Piv );

print @cols

set @query = 'SELECT ' + @cols + ' from
(
select Product,
OrderCount
from #sgs
) x
pivot
(
count(OrderCount)
for Product in (' + @cols + ')
) p '

select @query

SELECT [Suits], [waistcoat], [Tie]
from (
select Product,
OrderCount
from #sgs) x
pivot ( count(OrderCount) for Product in ([Suits], [Tie], [waistcoat])) p

execute(@query)


#4

yes that is if you want to do it dynamically. lets say you have more products and you want to this dynamically.
Look at the result of

select @query
I do notice though there is a problem with the solution provided. it does count Jackets and/or Trousers as Suits when there is an uneven pair.