question

newbie32768 avatar image
newbie32768 asked

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
sql-serverpivotcross-tab
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image
Usman Butt answered
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/
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

newbie32768 avatar image
newbie32768 answered
Thanks Usman, I'll check it out.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.