x

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

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

newbie32768 gravatar image

newbie32768
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][1] by Mr. Jeff Moden. This will help you a lot.

[1]: http://www.sqlservercentral.com/articles/Crosstab/65048/
more ▼

answered Feb 08, 2012 at 08:01 AM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

(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

newbie32768 gravatar image

newbie32768
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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x343
x77
x6

asked: Feb 08, 2012 at 06:57 AM

Seen: 690 times

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