March 25, 2013 at 7:44 pm
All,
I have data in numerous tables. The first 2 tables are simple:
tblProducts
-----------------
ProductID
ProductName
CategoryID
tblCategories
----------------
CategoryID
CategoryName
Each product links to a category. That part is easy as I can return the values I need from both tables for the ProductID passed in, like this:
ProductID | ProductName | CategoryID | CategoryName
--------------------------------------------------------------
13 | Magnets | 1 | Misc.
However, I have a third table that will always have 2 records for every Product:
tblProductPricing
----------------
ProductPricingID
ProductID
ProductQuantity (int)
ProductCost
Every product is sold in quantity of 1 and some other quantity (let's say 10). The cost for 1 is $1, the cost for 10 is $8 because there's a discount for buying in bulk.
How can I get the details of the Product plus the 2 records from tblProductPricing in a single record, like this:
ProductID | ProductName | CategoryID | CategoryName | ProductSize1 | ProductCost1 | ProductSize2 | ProductCost2
--------------------------------------------------------------
13 | Magnets | 1 | Misc. | 1 | $1 | 10 | $8
I can't figure it out without seemingly terrible code and I'd like to know if there's an easy way to do this.
Thanks,
Mark
March 25, 2013 at 11:44 pm
You could do something like the following, if you will always have 2 product prices, with 1 that has a quantity of 1.
select ProductID, ProductName, CategoryID, CategoryName, pp1.ProductQuantity, pp1.ProductCost, ppo.ProductQuantity, ppo.ProductCost
from Product p
inner join Category c on p.CategoryID = c.CategoryID
inner join ProductPricing pp1 on p.ProductID = pp1.productID and pp1.ProductQuantity = 1
inner join ProductPricing ppo on p.ProductID = ppo.productID and ppo.ProductQuantity <> 1
March 26, 2013 at 3:02 am
mickyT (3/25/2013)
You could do something like the following, if you will always have 2 product prices, with 1 that has a quantity of 1.
select ProductID, ProductName, CategoryID, CategoryName, pp1.ProductQuantity, pp1.ProductCost, ppo.ProductQuantity, ppo.ProductCost
from Product p
inner join Category c on p.CategoryID = c.CategoryID
inner join ProductPricing pp1 on p.ProductID = pp1.productID and pp1.ProductQuantity = 1
inner join ProductPricing ppo on p.ProductID = ppo.productID and ppo.ProductQuantity <> 1
If the above solution doesn't get you the expected results....then please post some sample data for further assistance.
March 26, 2013 at 7:56 am
The solution that MickyT posted looks like it will probably work for the short term. I can tell you from experience that if business is offering quantity discounts currently, they will add more levels in the future. It would be in your best interest to make your solution scalable so that it doesn't matter how many levels of discounts there are.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 26, 2013 at 6:18 pm
micky - I'll check that out.
Sean - what did you have in mind? What can I do to make it more scalable?
Thanks,
Mark
March 26, 2013 at 6:44 pm
Mark Eckeard (3/26/2013)
Sean - what did you have in mind? What can I do to make it more scalable?
I don't think you can do it totally dynamical, but you may come closer by using crosstab (also known as PIVOT) queries.
Type "crosstab query" in BOL for some examples.
_____________
Code for TallyGenerator
March 26, 2013 at 7:31 pm
Hi
A good article for crosstabs is http://www.sqlservercentral.com/articles/T-SQL/63681/
Here's an example that uses a crosstab allowing for up to 5 different product pricings
-- Set up some test data
;with product as (
SELECT *
FROM (VALUES
(1, 'Beer', 1)
,(2, 'Wine', 1)
,(3, 'Bourbon', 1)
,(4, 'Crisps', 2)
,(5, 'Nuts', 2)
) AS product(ProductId, ProductName,CategoryID)
)
,category as (
SELECT *
FROM (VALUES
(1,'Beverages')
,(2,'Snacks')
) AS product(CategoryID, CategoryName)
)
,productprices as (
SELECT *
FROM (VALUES
(1,1,2.00)
,(1,12,20.00)
,(1,24,36.00)
,(2,1,12.00)
,(2,6,60.00)
,(3,1,45.00)
,(4,10,12.00)
,(5,5,10.00)
,(5,10,16.00)
) AS product(ProductID, ProductQuantity, ProductCost)
)
-- Create query to pivot data
SELECT p.ProductID, p.ProductName, c.CategoryID, c.CategoryName
,MAX(CASE WHEN pp.Seq = 1 THEN pp.ProductQuantity ELSE null END) ProductQuantity1
,MAX(CASE WHEN pp.Seq = 1 THEN pp.ProductCost ELSE null END) ProductCost1
,MAX(CASE WHEN pp.Seq = 2 THEN pp.ProductQuantity ELSE null END) ProductQuantity2
,MAX(CASE WHEN pp.Seq = 2 THEN pp.ProductCost ELSE null END) ProductCost2
,MAX(CASE WHEN pp.Seq = 3 THEN pp.ProductQuantity ELSE null END) ProductQuantity3
,MAX(CASE WHEN pp.Seq = 3 THEN pp.ProductCost ELSE null END) ProductCost3
,MAX(CASE WHEN pp.Seq = 4 THEN pp.ProductQuantity ELSE null END) ProductQuantity4
,MAX(CASE WHEN pp.Seq = 4 THEN pp.ProductCost ELSE null END) ProductCost4
,MAX(CASE WHEN pp.Seq = 5 THEN pp.ProductQuantity ELSE null END) ProductQuantity5
,MAX(CASE WHEN pp.Seq = 5 THEN pp.ProductCost ELSE null END) ProductCost5
FROM Product p
INNER JOIN Category c ON p.CategoryID = c.CategoryID
INNER JOIN (
SELECT ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY ProductQuantity) Seq, ProductID, ProductQuantity, ProductCost
FROM ProductPrices
) pp ON p.ProductId = pp.ProductID
GROUP BY p.ProductID, p.ProductName, c.CategoryID, c.CategoryName
March 27, 2013 at 7:28 am
Mark Eckeard (3/26/2013)
micky - I'll check that out.Sean - what did you have in mind? What can I do to make it more scalable?
Thanks,
Mark
A dynamic cross tab is what I have in mind. I would like to see some ddl and sample data that is representative of your situation. Micky did a nice job of creating some for you, let me know if that will work.
I do have a question though about what you are doing here. It seems like what you have is quantity break thresholds. In you example you said you have a product that costs $1 at a quantity of 1 and $8 when purchasing 10. Seems to me that you have this off a little bit from how I would build this. I would make the cost at 10 be 80¢. Otherwise you don't know what to charge when the customer purchases 11. Unless the business rules in that case would be charge $8 for 10 and $1 for the remainder.
Let me know about the ddl and sample data. Once I have that we toss this into a dynamic cross tab and it will scale for any number of quantity discounts.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 28, 2013 at 3:59 am
This is how you can transform Mickey's query into a Dynamic Cross tab:
Declare @sql Varchar(MAX)
-- Set up some test data
Create table product(ProductId Int, ProductName Varchar(30),CategoryID Int)
Create table category(CategoryID Int, CategoryName Varchar(30))
Create table productprices(ProductID Int, ProductQuantity Int, ProductCost Float)
Insert Into product
Select1, 'Beer', 1
Union ALL
Select 2, 'Wine', 1
Union ALL
Select 3, 'Bourbon', 1
Union ALL
Select 4, 'Crisps', 2
Union ALL
Select 5, 'Nuts', 2
Insert Into category
Select1,'Beverages'
Union ALL
Select 2,'Snacks'
Insert Into productprices
Select1,1,2.00
Union ALL
Select 1,12,20.00
Union ALL
Select 1,24,36.00
Union ALL
Select 2,1,12.00
Union ALL
Select 2,6,60.00
Union ALL
Select 3,1,45.00
Union ALL
Select 4,10,12.00
Union ALL
Select 5,5,10.00
Union ALL
Select 5,10,16.00
-- Create query to dynamically pivot data
Select @sql = 'SELECT p.ProductID, p.ProductName, c.CategoryID, c.CategoryName, '
Select @sql = @sql + STUFF((Select DISTINCT ',MAX(CASE WHEN pp.Seq = ' + Cast(pp.Seq As Varchar) + ' THEN pp.ProductQuantity ELSE null END) As ProductQuantity' + Cast(pp.Seq As Varchar)
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY ProductQuantity) Seq, ProductID, ProductQuantity, ProductCost
FROM productprices
) pp FOR XML PATH('')),1,1,'') + ', '
Select @sql = @sql + STUFF((Select DISTINCT ',MAX(CASE WHEN pp.Seq = ' + Cast(pp.Seq As Varchar) + ' THEN pp.ProductCost ELSE null END) As ProductCost' + Cast(pp.Seq As Varchar)
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY ProductQuantity) Seq, ProductID, ProductQuantity, ProductCost
FROM productprices
) pp FOR XML PATH('')),1,1,'')
Select @sql = @sql + ' FROM product p
INNER JOIN category c ON p.CategoryID = c.CategoryID
INNER JOIN (
SELECT ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY ProductQuantity) Seq, ProductID, ProductQuantity, ProductCost
FROM productprices
) pp ON p.ProductId = pp.ProductID
GROUP BY p.ProductID, p.ProductName, c.CategoryID, c.CategoryName '
Execute(@sql)
Hope this helps.
March 29, 2013 at 5:47 pm
one easy solution, using xml path.
SELECT P.PRODUCTID,P.PRODUCTNAME,C.CategoryID,C.CATEGORYNAME,
STUFF((SELECT ',' + convert(varchar,PP.ProductQuantity)
FROM ProductPricing PP
WHERE PP.PRODUCTID=P.PRODUCTID
FOR XML PATH('')),1,1,'') AS ProductQuantity,
STUFF((SELECT ',' + convert(varchar,PP.ProductCost)
FROM ProductPricing PP
WHERE PP.PRODUCTID=P.PRODUCTID
FOR XML PATH('')),1,1,'') AS ProductCost
FROM PRODUCT P
JOINCategories C ON C.CategoryID=P.CategoryID
March 30, 2013 at 11:51 am
umarrizwan (3/29/2013)
one easy solution, using xml path.SELECT P.PRODUCTID,P.PRODUCTNAME,C.CategoryID,C.CATEGORYNAME,
STUFF((SELECT ',' + convert(varchar,PP.ProductQuantity)
FROM ProductPricing PP
WHERE PP.PRODUCTID=P.PRODUCTID
FOR XML PATH('')),1,1,'') AS ProductQuantity,
STUFF((SELECT ',' + convert(varchar,PP.ProductCost)
FROM ProductPricing PP
WHERE PP.PRODUCTID=P.PRODUCTID
FOR XML PATH('')),1,1,'') AS ProductCost
FROM PRODUCT P
JOINCategories C ON C.CategoryID=P.CategoryID
Although that code works, it doesn't come close the the requested output.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 30, 2013 at 12:00 pm
vinu512 (3/28/2013)
This is how you can transform Mickey's query into a Dynamic Cross tab:
Declare @sql Varchar(MAX)
-- Set up some test data
Create table product(ProductId Int, ProductName Varchar(30),CategoryID Int)
Create table category(CategoryID Int, CategoryName Varchar(30))
Create table productprices(ProductID Int, ProductQuantity Int, ProductCost Float)
Insert Into product
Select1, 'Beer', 1
Union ALL
Select 2, 'Wine', 1
Union ALL
Select 3, 'Bourbon', 1
Union ALL
Select 4, 'Crisps', 2
Union ALL
Select 5, 'Nuts', 2
Insert Into category
Select1,'Beverages'
Union ALL
Select 2,'Snacks'
Insert Into productprices
Select1,1,2.00
Union ALL
Select 1,12,20.00
Union ALL
Select 1,24,36.00
Union ALL
Select 2,1,12.00
Union ALL
Select 2,6,60.00
Union ALL
Select 3,1,45.00
Union ALL
Select 4,10,12.00
Union ALL
Select 5,5,10.00
Union ALL
Select 5,10,16.00
-- Create query to dynamically pivot data
Select @sql = 'SELECT p.ProductID, p.ProductName, c.CategoryID, c.CategoryName, '
Select @sql = @sql + STUFF((Select DISTINCT ',MAX(CASE WHEN pp.Seq = ' + Cast(pp.Seq As Varchar) + ' THEN pp.ProductQuantity ELSE null END) As ProductQuantity' + Cast(pp.Seq As Varchar)
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY ProductQuantity) Seq, ProductID, ProductQuantity, ProductCost
FROM productprices
) pp FOR XML PATH('')),1,1,'') + ', '
Select @sql = @sql + STUFF((Select DISTINCT ',MAX(CASE WHEN pp.Seq = ' + Cast(pp.Seq As Varchar) + ' THEN pp.ProductCost ELSE null END) As ProductCost' + Cast(pp.Seq As Varchar)
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY ProductQuantity) Seq, ProductID, ProductQuantity, ProductCost
FROM productprices
) pp FOR XML PATH('')),1,1,'')
Select @sql = @sql + ' FROM product p
INNER JOIN category c ON p.CategoryID = c.CategoryID
INNER JOIN (
SELECT ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY ProductQuantity) Seq, ProductID, ProductQuantity, ProductCost
FROM productprices
) pp ON p.ProductId = pp.ProductID
GROUP BY p.ProductID, p.ProductName, c.CategoryID, c.CategoryName '
Execute(@sql)
Hope this helps.
Good example of a dynamic cross tab, Vinu. Can you change it to put output columns in the same order as requested?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 30, 2013 at 12:06 pm
Nice examples everyone, thanks!
Sean - I can only sell items individually and then in a second, pre-set quantity but that second quantity can differ between products. It could be 15 for Product A but 11 for Product B.
I've designed the table for quantities so that more can be added in the future. Although I don't see that happening, I didn't want to create a flat table design and find out in 6 months it's changing and I'd have to redesign my tables.
Mark
April 1, 2013 at 1:11 am
Jeff Moden (3/30/2013)
vinu512 (3/28/2013)
This is how you can transform Mickey's query into a Dynamic Cross tab:
Declare @sql Varchar(MAX)
-- Set up some test data
Create table product(ProductId Int, ProductName Varchar(30),CategoryID Int)
Create table category(CategoryID Int, CategoryName Varchar(30))
Create table productprices(ProductID Int, ProductQuantity Int, ProductCost Float)
Insert Into product
Select1, 'Beer', 1
Union ALL
Select 2, 'Wine', 1
Union ALL
Select 3, 'Bourbon', 1
Union ALL
Select 4, 'Crisps', 2
Union ALL
Select 5, 'Nuts', 2
Insert Into category
Select1,'Beverages'
Union ALL
Select 2,'Snacks'
Insert Into productprices
Select1,1,2.00
Union ALL
Select 1,12,20.00
Union ALL
Select 1,24,36.00
Union ALL
Select 2,1,12.00
Union ALL
Select 2,6,60.00
Union ALL
Select 3,1,45.00
Union ALL
Select 4,10,12.00
Union ALL
Select 5,5,10.00
Union ALL
Select 5,10,16.00
-- Create query to dynamically pivot data
Select @sql = 'SELECT p.ProductID, p.ProductName, c.CategoryID, c.CategoryName, '
Select @sql = @sql + STUFF((Select DISTINCT ',MAX(CASE WHEN pp.Seq = ' + Cast(pp.Seq As Varchar) + ' THEN pp.ProductQuantity ELSE null END) As ProductQuantity' + Cast(pp.Seq As Varchar)
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY ProductQuantity) Seq, ProductID, ProductQuantity, ProductCost
FROM productprices
) pp FOR XML PATH('')),1,1,'') + ', '
Select @sql = @sql + STUFF((Select DISTINCT ',MAX(CASE WHEN pp.Seq = ' + Cast(pp.Seq As Varchar) + ' THEN pp.ProductCost ELSE null END) As ProductCost' + Cast(pp.Seq As Varchar)
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY ProductQuantity) Seq, ProductID, ProductQuantity, ProductCost
FROM productprices
) pp FOR XML PATH('')),1,1,'')
Select @sql = @sql + ' FROM product p
INNER JOIN category c ON p.CategoryID = c.CategoryID
INNER JOIN (
SELECT ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY ProductQuantity) Seq, ProductID, ProductQuantity, ProductCost
FROM productprices
) pp ON p.ProductId = pp.ProductID
GROUP BY p.ProductID, p.ProductName, c.CategoryID, c.CategoryName '
Execute(@sql)
Hope this helps.
Good example of a dynamic cross tab, Vinu. Can you change it to put output columns in the same order as requested?
Yes Jeff,
I did manage to get the columns in the order requested by tweaking the code i posted earlier.
Here is the new code with the desired order of columns:
Declare @sql Varchar(MAX)
-- Set up some test data
Create table product(ProductId Int, ProductName Varchar(30),CategoryID Int)
Create table category(CategoryID Int, CategoryName Varchar(30))
Create table productprices(ProductID Int, ProductQuantity Int, ProductCost Float)
Insert Into product
Select1, 'Beer', 1
Union ALL
Select 2, 'Wine', 1
Union ALL
Select 3, 'Bourbon', 1
Union ALL
Select 4, 'Crisps', 2
Union ALL
Select 5, 'Nuts', 2
Insert Into category
Select1,'Beverages'
Union ALL
Select 2,'Snacks'
Insert Into productprices
Select1,1,2.00
Union ALL
Select 1,12,20.00
Union ALL
Select 1,24,36.00
Union ALL
Select 2,1,12.00
Union ALL
Select 2,6,60.00
Union ALL
Select 3,1,45.00
Union ALL
Select 4,10,12.00
Union ALL
Select 5,5,10.00
Union ALL
Select 5,10,16.00
-- Create query to dynamically pivot data
Select @sql = 'SELECT p.ProductID, p.ProductName, c.CategoryID, c.CategoryName, '
Select @sql = @sql + STUFF((Select DISTINCT ',MAX(CASE WHEN pp.Seq = ' + Cast(pp.Seq As Varchar) + ' THEN pp.ProductQuantity ELSE null END) As ProductQuantity' + Cast(pp.Seq As Varchar) + ',MAX(CASE WHEN pp.Seq = ' + Cast(pp.Seq As Varchar) + ' THEN pp.ProductCost ELSE null END) As ProductCost' + Cast(pp.Seq As Varchar)
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY ProductQuantity) Seq, ProductID, ProductQuantity, ProductCost
FROM productprices
) pp FOR XML PATH('')),1,1,'')
Select @sql = @sql + ' FROM product p
INNER JOIN category c ON p.CategoryID = c.CategoryID
INNER JOIN (
SELECT ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY ProductQuantity) Seq, ProductID, ProductQuantity, ProductCost
FROM productprices
) pp ON p.ProductId = pp.ProductID
GROUP BY p.ProductID, p.ProductName, c.CategoryID, c.CategoryName '
Execute(@sql)
P.S.: Jeff, I'm gonna tell my dba friend's that Jeff Moden liked my solution.......ROFL!!!!!! :-D:-D
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply