variation on the pivot theme

/*

I have a database with customers who can order 1 or more products. The product has a shelf life that I retrieve from a ProductSpecs table based on a ValidFrom/ValidUntil date pair. I'm trying to retrieve a resultset that is as follows:

CustomerName Product1 Price1                                  Product2 Price2      ProductCount
------------ -------- --------------------------------------- -------- ----------- ------------
Peter        Spinach  12.50                                   Lettuce  24          2
Jane         Ketchup  61.00                                   NULL     NULL        1

I've got a query that retrieves the following resultset

Customer                       Product                        Price
------------------------------ ------------------------------ ---------------------------------------
Peter                          Spinach                        12.50
Peter                          Lettuce                        24.00
Jane                           Ketchup                        61.00

How can I achieve my desired result?

Here's the script, it creates tables, fills them, generates 2 resultsets and cleans up after itself:

Thanks in advance

Henk

*/
create table Customers (CustomerId int, CustomerName char(30))
insert into Customers select 1,'Peter' union all select 2,'Jane'
create table Products (ProductId int, ProductName char(30))
insert into Products select 5, 'Spinach'union all select 6, 'Lettuce'union all select 7, 'Ketchup' union all select 8, 'Hamburger'
create table CustomersProducts (CustomerId int, ProductSpecId int)
insert into CustomersProducts select 1, 2 union all select 1, 4 union all select 2, 12
create table ProductSpecs (ProductSpecId int, ProductId int, Price numeric(5,2), ValidFrom datetime not null, ValidUntil datetime null)
insert into ProductSpecs select 1, 5, 12, getdate()-50, getdate() - 1
    union all select 2, 5, 12.50, getdate(), null
    union all select 3, 6, 41, getdate() - 50, getdate() - 21
    union all select 4, 6, 24, getdate() - 20, null
    union all select 12, 7, 61, getdate() - 50, null
    union all select 13, 8, 173, getdate() - 50, null
select MyResultset.* from (select c.CustomerName Customer, p.ProductName Product, ps.Price from Customers c join CustomersProducts cp on c.CustomerId = cp.CustomerId join ProductSpecs ps on cp.ProductSpecId = ps.ProductSpecId 
join products p on ps.ProductId = p.productid) as MyResultset
select DesiredResultset.* from (select 'Peter' CustomerName, 'Spinach' Product1, 12.50 Price1, 'Lettuce' Product2, 24 Price2, 2 ProductCount union all select 'Jane', 'Ketchup' Product1, 61 Price1, NULL Product2, NULL Price2, 1 ProductCount) DesiredResultSet
drop table customers
drop table products
drop table customersproducts
drop table productspecs