title: Transform and pivot like in mysql question
body: <p>I hope someone could show me the light.</p>
  
  <p>I'm trying since a long time to get the result o some sales by week, day and year in one query with no chance.</p>
  
  <p>I have found a syntax that can help me but dont work in mysql.</p>
  
  <pre><code>    TRANSFORM 
     Sum(Cantidad) AS Ventas 
  SELECT 
     Producto, Cantidad 
  FROM 
     Pedidos 
  WHERE 
     Fecha Between #01-01-1998# And #12-31-1998# 
  GROUP BY 
     Producto 
  ORDER BY 
     Producto 
  PIVOT 
     DatePart("m", Fecha) 
  </code></pre>
  
  <p>My real query is this one:</p>
  
  <pre><code>select sum(orders.final_price) AS total
       , orders.id_comercial
       , vendedores.nombre
  from orders
     , vendedores
  WHERE orders.id_comercial = vendedores.id
    AND DATE(orders.date_purchased) = DATE(NOW())
  GROUP BY orders.id_comercial
  </code></pre>
  
  <p>I really will appreciate some ideas, it's maybe to late in teh night here in spain ;)</p>
  
  <p>The result of my query is this one:</p>
  
  <pre><code>total         id_comercial         nombre
  740.83        24        AITOR ANGULO
  2069.37       29        FERNANDO I
  482.05        32        JOSE ANDRES
  961.32        33        ALBERTO FERNANDEZ -CANTABRIA-
  908.66        34        GONZALO
  49.54         38        LUIS LOPEZ
  4082.42       39        JULEN DEL CURA
  1512.87       43        ALBERTO POBLACION
  1268.91       44        MASSIMO
  3269.08       45        JOSE RAMON BURGA
  56.49         47        EMPRESA
  2791.65       48        MARCO MORILLO
  1445.57       61        TINO
  869.73        63        SONIA
  1052.02       381       DAVID ARIAS
  </code></pre>
  
  <p>Really thnx to all.</p>