SQL View - Pivot

Question

SQL View - Pivot

bbranco

SSC Rookie

Points: 35
More actions
November 12, 2019 at 7:21 pm

#3697151

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?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246695 More actions · Answer 1

Is the maximum number of columns as shown in the image, or can this vary?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

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)

bbranco SSC Rookie Points: 35 More actions · Answer 3

There is no limit, but I believe the max they have is 6 levels.

bbranco SSC Rookie Points: 35 More actions · Answer 4

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?

David Burrows SSC Guru Points: 65138 More actions · Answer 5

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.

bbranco SSC Rookie Points: 35 More actions · Answer 6

Thanks for the reply, but I can't have the item numbers in there or the values in the FROM Values area.

David Burrows SSC Guru Points: 65138 More actions · Answer 7

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.