SQL View - Pivot
-
I have the following data in a table, its pricing for items, so if a customer buys 275 of .250BP, they pay .043
I need to get this data in a view like the sample below.
Can someone help with this?
-
Is the maximum number of columns as shown in the image, or can this vary?
- bbranco wrote:
I have the following data in a table, its pricing for items, so if a customer buys 275 of .250BP, they pay .043
I need to get this data in a view like the sample below.
Can someone help with this?
Look at what you wrote and then tell me why it would be .043, which isn't even an option on your spreadsheet. Also, .43 isn't the correct number if someone buys 275.
Last but not least, if you really want some help, then help us help you by doing a read'n'heed the article at the first link in my signature line below. And, yes, we need an answer to Phil's question above, as well.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
There is no limit, but I believe the max they have is 6 levels.
-
Sorry...
I have the following data in a table, its pricing for items, so if a customer buys 275 of .250BP, they pay .47
Do you want me to delete this and post it based on your article? or can you help?
-
This looks like a standard pivot, for six levels thus
SELECT[No_],
MAX(CASE WHEN Num = 1 THEN [Minimum Quantity] END) AS [MinQty1],
MAX(CASE WHEN Num = 1 THEN [Maximum Quantity] END) AS [MaxQty1],
MAX(CASE WHEN Num = 1 THEN [Unit Price] END) AS [Price1],
MAX(CASE WHEN Num = 2 THEN [Minimum Quantity] END) AS [MinQty2],
MAX(CASE WHEN Num = 2 THEN [Maximum Quantity] END) AS [MaxQty2],
MAX(CASE WHEN Num = 2 THEN [Unit Price] END) AS [Price2],
MAX(CASE WHEN Num = 3 THEN [Minimum Quantity] END) AS [MinQty3],
MAX(CASE WHEN Num = 3 THEN [Maximum Quantity] END) AS [MaxQty3],
MAX(CASE WHEN Num = 3 THEN [Unit Price] END) AS [Price3],
MAX(CASE WHEN Num = 4 THEN [Minimum Quantity] END) AS [MinQty4],
MAX(CASE WHEN Num = 4 THEN [Maximum Quantity] END) AS [MaxQty4],
MAX(CASE WHEN Num = 4 THEN [Unit Price] END) AS [Price4],
MAX(CASE WHEN Num = 5 THEN [Minimum Quantity] END) AS [MinQty5],
MAX(CASE WHEN Num = 5 THEN [Maximum Quantity] END) AS [MaxQty5],
MAX(CASE WHEN Num = 5 THEN [Unit Price] END) AS [Price5],
MAX(CASE WHEN Num = 6 THEN [Minimum Quantity] END) AS [MinQty6],
MAX(CASE WHEN Num = 6 THEN [Maximum Quantity] END) AS [MaxQty6],
MAX(CASE WHEN Num = 6 THEN [Unit Price] END) AS [Price6]
FROM(
SELECT[No_],[Minimum Quantity],[Maximum Quantity],[Unit Price],
ROW_NUMBER() OVER (PARTITION BY [No_] ORDER BY [Minimum Quantity]) AS [Num]
FROM(VALUES
('.250BP',0.001,249.999,0.51),
('.250BP',250.0,499.999,0.47),
('.250BP',500.0,100000000.0,0.43),
('.250BR1WRAP',0.001,1999.999,0.41),
('.250BR1WRAP',2000.0,100000000.0,0.39)
) a ([No_],[Minimum Quantity],[Maximum Quantity],[Unit Price])
) b
GROUPBY [No_];For more than six or to limit the number levels by their presence you will have to convert it to dynamic sql.
Far away is close at hand in the images of elsewhere.
Anon. -
Thanks for the reply, but I can't have the item numbers in there or the values in the FROM Values area.
- bbranco wrote:
Thanks for the reply, but I can't have the item numbers in there or the values in the FROM Values area.
Sorry but I do not understand your reply.
What I posted was a working example for you to modify to suit your needs.
It should work if you replace
FROM(VALUES
('.250BP',0.001,249.999,0.51),
('.250BP',250.0,499.999,0.47),
('.250BP',500.0,100000000.0,0.43),
('.250BR1WRAP',0.001,1999.999,0.41),
('.250BR1WRAP',2000.0,100000000.0,0.39)
) a ([No_],[Minimum Quantity],[Maximum Quantity],[Unit Price])with
FROM [tablename]
where [tablename] is the name of your table.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply