I have a table with three columns:

Guid | event | timestamp
1236| page-view | 1234
1236 | product-view | 1235
1236 | page-view | 1237
2025 | add-to-cart
2025 | purchase

I want to write a query not using case statement to show the events in columns with the sum of their occurrences. I don’t want to use case for this since the events keep increasing and there are around 200 of them now.

Output I am looking for is something like this:

GUid | page-view | product-view | add-to-cart | purchase 

1236 | 2 | 1 | 0 | 0 
2025 | 0 | 0 | 1 | 1 

CodePudding user response：

SELECT  a.Guid ,
    ifnull ((SELECT count (Guid ) from table_a where  event in('page-view') and Guid = a.Guid  GROUP by Guid  ),0) as [page-view]  ,
    ifnull((SELECT count (Guid ) from table_a where  event in('product-view') and Guid = a.Guid  GROUP by Guid  ),0) as [product-view]  ,
    ifnull((SELECT count (Guid ) from table_a where  event in('add-to-cart') and Guid = a.Guid  GROUP by Guid  ),0) as [add-to-cart]  ,         
    ifnull((SELECT count (Guid ) from table_a where  event in('purchase') and Guid = a.Guid  GROUP by Guid  ),0) as [purchase ]  
        FROM table_a a
        GROUP by Guid 

CodePudding user response：

Here's a clean solution using pivot.

select   Guid
        ,count([page-view])    as 'page-view'
        ,count([product-view]) as 'product-view'
        ,count([add-to-cart])  as 'add-to-cart'
        ,count([purchase])     as 'purchase'
from    (
        select  * 
               ,rank() over (order by Guid) as rnk
         from t 
         ) tmp
         pivot
         (
         max(rnk)
         for event in([page-view],[product-view],[add-to-cart],[purchase])
         ) pvt
group by Guid

Fiddle