How could create a crosstab SQL query with Django ORM?

Question

Using Django 1.1, how could I create a crosstab (pivot table) SQL query using the ORM?

UPDATED: These are the models and output requirements:

class Store(models.Model):
 name = models.CharField(max_length=255)
 ...

class Order(models.Model):
 store = models.ForeignKey(Store, blank=True, null=True, related_name='orders')
 description = models.CharField(_('Description'), max_length=255)
 quantity = models.IntegerField(blank=True, null=True)  
 type_detail = models.CharField(_('Type Detail'), max_length=255)
 slug = models.SlugField(blank=True)
 cost = models.DecimalField(_("Cost"), max_digits=14, decimal_places=2)
 modified = models.DateTimeField(_('modified'), auto_now=True)

Currently the view is showing data like so:

Store   | Type Detail  | Quantity 
----------------------------------
Walmart | Floor polish | 2        
Walmart | Tiles        | 1        
Walmart | Milk         | 4     
Another | Floor polish | 2        
Another | Tiles        | 1        
Another | Milk         | 4

I want to pivot this to view the data like so:

For a store I need to know quantities

Store | Floor polish  | Tiles | Milk
------------------------------------------------
Walmart | 2             | 1     | 4
Another | 2             | 1     | 4

I hope that explains what I need.

Answer 1

You can do this in the template as follows (assuming you are passing line_items to your template and also assuming store.name is a unique property):

{% regroup line_items by store.name as store_items %}
{% for store in store_items %}    
  <tr>
    <td>{{ store.grouper }}</td>
    {% for item in store.list %}
      <td>{{ item.count }}</td>
    {% endfor %}
  </tr>
{% endfor %}

This would work if all stores have the same inventory, otherwise you'll need to fill the gaps in the view (returning 0's for missing inventory items, for example)

Answer 2

Don't know about Django, but here's plain SQL

SELECT Store,
SUM(CASE WHEN Type_Detail = 'Floor polish' THEN Quantity ELSE 0 END) as 'Floor polish',
SUM(CASE WHEN Type_Detail = 'Tiles' THEN Quantity ELSE 0 END) as 'Tiles',
SUM(CASE WHEN Type_Detail = 'Milk' THEN Quantity ELSE 0 END) as 'Milk'
FROM Order
GROUP BY Store