split a column to a few columns by conditions-CodePudding

I have 3 tables.

company_report (old_product_code,count)
dictionary(old_product_code, new_product_code)
myreport(new_product_code, package_of_1, package_of_3, package_of_5)

In my company_report table I have sometimes 2 or 3 old_product_code that fit to one new_product_code

The result I want is my_report table.

A picture of an example attached.

CodePudding user response：

It looks like you just want a join with conditional aggregation

SELECT
  d.new_product_code,
  Package1 = SUM(CASE WHEN REPLACE(cr.old_product_code, d.new_product_code, '') = '1' THEN count END),
  Package3 = SUM(CASE WHEN REPLACE(cr.old_product_code, d.new_product_code, '') = '3' THEN count END),
  Package5 = SUM(CASE WHEN REPLACE(cr.old_product_code, d.new_product_code, '') = '5' THEN count END)
FROM company_report cr
JOIN dictionary d ON d.old_product_code = cr.old_product_code
GROUP BY
  d.new_product_code;

CodePudding user response：

select d.new_code 
,case when SUBSTRING(fieldName, PATINDEX('%[0-9]%', fieldName), LEN(fieldName))='1' then count end as package_1
,case when SUBSTRING(fieldName, PATINDEX('%[0-9]%', fieldName), LEN(fieldName))='3' then count end as package_3
,case when SUBSTRING(fieldName, PATINDEX('%[0-9]%', fieldName), LEN(fieldName))='5' then count end as package_5
from dictionary d
left join company_report c
on d.old_code=c.old_product_code
group by d.new_code