SQL output query-CodePudding

INPUT

REQUIRED OUTPUT

Hi,

I am trying to convert the SQL table from input to the output format.

Input file has keys- Material, Variant, Scale

To reduce the number of rows, scale based pricing is being implemented at my organization. Not sure how do we use input format (image 1) to convert to output format (image 2)

output table keys- product, variant, scale_low, scale_high

This is going to reduce the number of pricing records we are going to enter in the system.

Requesting help from anyone who has dealt with this issue in the past.

CodePudding user response：

It looks like you could just take the min and max values like so:

Select product, variant, 
min(scale) as scale_low, max(scale) as scale_high, 
Min(CondValue) as low_amt, Max(CondValue) as high_amt
group by product, variant
order by product, variant;

CodePudding user response：

I'd consider doing it like this:

WITH bands AS (
  SELECT 1 as lo, 10 as hi 
  UNION ALL SELECT 11, 30
  UNION ALL SELECT 31, 50
  UNION ALL SELECT 51, 150
  UNION ALL SELECT 151, 999999
)
SELECT
  Material, Variant, Lo, Hi, MIN(Value), MAX(Value)
FROM
  t
  INNER JOIN bands ON t.Scale BETWEEN lo and hi
GROUP BY Material, Variant, Lo, Hi

Or i'd turn that bands into a real table so I could just tweak them without having to rewrite my sql