Q:

Pivoting data with CASE expression

I have the following query which gets the store, the week number and the sum of products sold : 
select *
from 
(
  select store, week, xCount
  from yt
) src
pivot
(
  sum(xcount)
  for week in ([1], [2], [3])
) piv;

Is it possible to get the same result with CASE expression ?

A:

You can pivot with conditional aggregation as follows:
select 
    store, 
    sum(case when week = 1 then xcount else 0 end) week1,
    sum(case when week = 2 then xcount else 0 end) week2,
    sum(case when week = 3 then xcount else 0 end) week3
from yt
group by store

I tend to prefer conditional aggregation over database-specific pivot syntaxes:

conditional aggregation is more flexible than pivot
the syntax works across most databases, so it is much more portable
performance is usually equivalent

