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


 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
more ▼

asked Feb 08, 2012 at 06:57 AM in Default

avatar image

15 1 1 2

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

As the real data could be quite different and of a larger scale, you need to use DYNAMIC QUERY. I would recommend this article by Mr. Jeff Moden. This will help you a lot.

more ▼

answered Feb 08, 2012 at 08:01 AM

avatar image

Usman Butt
13.9k 6 13 21

(comments are locked)
10|1200 characters needed characters left

Thanks Usman, I'll check it out.

more ▼

answered Feb 08, 2012 at 05:34 PM

avatar image

15 1 1 2

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Feb 08, 2012 at 06:57 AM

Seen: 834 times

Last Updated: Feb 08, 2012 at 06:32 PM

Copyright 2016 Redgate Software. Privacy Policy