question

/* 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