sql - Informix grouping based on static values returned in query -

i have query works , returns me bunch of product numbers, want group these product numbers down product group level. these groups not exist in database.

i have list this:

'metro 60'  '63782-1-4000','63782-1-5000','63778-1-4000','63778-1-5000','63780-1-5000','63776-1-4000','63776-1-5000','63772-1-4000','63772-1-5000','63774-1-4000','63774-1-5000','63784-1-5000','63786-1-5000' 'elements wa'   '63782-1-4000','63782-1-5000','63778-1-4000','63778-1-5000','63780-1-5000','63776-1-4000','63776-1-5000','63772-1-4000','63772-1-5000','63774-1-4000','63774-1-5000','63784-1-5000','63786-1-5000','63774-2-1000','63782-2-1000','63778-2-0200','63772-2-1000','63776-2-1000','67744-1-5000','63770-2-1000','63768-2-0200','63772-5-1000','63774-5-1000','63774-5-3000','63760-2-1000','63748-5-4000','66757-5-1000','63774-5-2000','63494-1-5000','63486-1-5000','63476-1-5000','63486-1-6000','63495-1-5000','63487-1-5000','63487-1-6000' 'indulgence'    '63782-1-4000','63782-1-5000','63778-1-4000','63778-1-5000','63780-1-5000','63776-1-4000','63776-1-5000','63772-1-4000','63772-1-5000','63774-1-4000','63774-1-5000','63784-1-5000','63786-1-5000','63774-2-1000','63782-2-1000','63778-2-0200','63772-2-1000','63776-2-1000','67744-1-5000','63770-2-1000','63768-2-0200','63772-5-1000','63774-5-1000','63774-5-3000','63760-2-1000','63748-5-4000','66757-5-1000','63774-5-2000','63494-1-5000','63486-1-5000','63476-1-5000','63486-1-6000','63495-1-5000','63487-1-5000','63487-1-6000','63530-1-5000','63536-1-5000','63534-1-5000','63532-1-4000','63532-1-5000','63528-1-5000','63531-1-5000','63537-1-5000','63535-1-5000','63533-1-5000','63529-1-5000' 

etc

how can group results of query return instead of product.part_code?

query:

select  product.part_code,     sum(product.cubic_qty*(salesstat.order_qty-salesstat.return_qty)) /1000 sales_qty,     sum(salesstat.net_amt-salesstat.return_net_amt) gross_revenue,     sum(salesstat.net_prod_amt - salesstat.return_nprod_amt) net_revenue,     decode(sum(product.cubic_qty*(salesstat.order_qty)), 0, null,         sum(salesstat.net_prod_amt)/sum(product.cubic_qty*(salesstat.order_qty))) *1000 net_asp,     decode(sum(product.cubic_qty*(salesstat.order_qty-salesstat.return_qty)), 0, null,         sum(salesstat.cost_amt-salesstat.return_net_amt) / (sum(product.cubic_qty*(salesstat.order_qty-salesstat.return_qty)) /1000)) cost_per_thou,     sum(salesstat.cost_amt-salesstat.return_cost_amt) total_cost,     sum(salesstat.net_prod_amt - salesstat.return_nprod_amt)  - sum(salesstat.cost_amt-salesstat.return_cost_amt) gross_profit,     decode(sum(product.cubic_qty*(salesstat.order_qty-salesstat.return_qty)), 0, null,         (sum(salesstat.net_prod_amt - salesstat.return_nprod_amt)  - sum(salesstat.cost_amt-salesstat.return_cost_amt)) / (sum(product.cubic_qty*(salesstat.order_qty-salesstat.return_qty)) /1000)) profit_per_thou    customer,     salesstat,     warereptdetl,     product,     maingrp   product.part_code = salesstat.part_code , product.part_code in ('63782-1-4000','63782-1-5000','63778-1-4000','63778-1-5000','63780-1-5000','63776-1-4000','63776-1-5000','63772-1-4000','63772-1-5000','63774-1-4000','63774-1-5000','63784-1-5000','63786-1-5000','63774-2-1000','63782-2-1000','63778-2-0200','63772-2-1000','63776-2-1000','67744-1-5000','63770-2-1000','63768-2-0200','63772-5-1000','63774-5-1000','63774-5-3000','63760-2-1000','63748-5-4000','66757-5-1000','63774-5-2000','63494-1-5000','63486-1-5000','63476-1-5000','63486-1-6000','63495-1-5000','63487-1-5000','63487-1-6000','63530-1-5000','63536-1-5000','63534-1-5000','63532-1-4000','63532-1-5000','63528-1-5000','63531-1-5000','63537-1-5000','63535-1-5000','63533-1-5000','63529-1-5000','63559-1-5000','63440-1-5000','63510-1-4000','63510-1-5000','63510-1-6000','63516-1-5000','63518-1-5000','63540-1-5000','63540-1-6000','63430-1-4000','63430-1-5000','63430-1-6000','63526-1-4000','63526-1-5000','63526-1-6000','63559-1-6000','63516-1-6000','63560-1-5000','63441-1-5000','63511-1-5000','63511-1-6000','63517-1-5000','63519-1-5000','63541-1-5000','63431-1-4000','63431-1-5000','63431-1-6000','63527-1-5000','63560-1-6000','63322-1-5003','63332-1-5003','63330-1-5003','63334-1-5003','63323-1-5003','63333-1-5003','63335-1-5003','63326-1-4000','63326-1-5000','63326-1-6000','63334-1-4000','63334-1-5000','63334-1-6000','63334-1-5004','63327-1-4000','63327-1-5000','63327-1-6000','63335-1-5000','63320-1-5000','63320-1-6000','63321-1-5000','63321-1-6000','63314-1-5000','63422-1-5000','63312-1-5000','63322-1-5000','63332-1-5000','63330-1-5000','63338-1-4000','63338-1-5000','63338-1-6000','63422-1-4000','63315-1-5000','63423-1-5000','63313-1-5000','63323-1-5000','63333-1-5000','63339-1-4000','63339-1-5000','63339-1-6000','63318-1-6000','63318-1-5000','63319-1-6000','63319-1-5000') , customer.cust_code = salesstat.cust_code , salesstat.rept_code = '0' , warereptdetl.ware_code = salesstat.ware_code , customer.cust_code in ('91826','22978','91247','48972','91386','91520','101472','98214','122586','108675','130703','120847','101155','96360','12005','34263','101517') , salesstat.year_num = 2015 , salesstat.stat_type_code = 'mly' , salesstat.int_num = 9 , salesstat.ord_ind in ('7','8') , maingrp.maingrp_code = salesstat.maingrp_code group product.part_code 

thanks in advance

edit:

i have gotten working , have managed pivot (crosstab) results still if there better way this, surely can use "in" somewhere rather line per product?

select  case trim(product.part_code)     when '63782-1-4000' 'metro 60'     when '63782-1-5000' 'metro 60'     when '63778-1-4000' 'metro 60'     when '63778-1-5000' 'metro 60'     when '63780-1-5000' 'metro 60'     when '63776-1-4000' 'metro 60'     when '63776-1-5000' 'metro 60'     when '63772-1-4000' 'metro 60'     when '63772-1-5000' 'metro 60'     when '63774-1-4000' 'metro 60'     when '63774-1-5000' 'metro 60'     when '63784-1-5000' 'metro 60'     when '63786-1-5000' 'metro 60'     when '63774-2-1000' 'non-standard pavers'     when '63782-2-1000' 'non-standard pavers'     when '63778-2-0200' 'non-standard pavers'     when '63772-2-1000' 'non-standard pavers'     when '63776-2-1000' 'non-standard pavers'     when '67744-1-5000' 'non-standard pavers'     when '63770-2-1000' 'non-standard pavers'     when '63768-2-0200' 'non-standard pavers'     when '63772-5-1000' 'non-standard pavers'     when '63774-5-1000' 'non-standard pavers'     when '63774-5-3000' 'non-standard pavers'     when '63760-2-1000' 'non-standard pavers'     when '63748-5-4000' 'non-standard pavers'     when '66757-5-1000' 'non-standard pavers'     when '63774-5-2000' 'non-standard pavers'     when '63494-1-5000' 'elements wa'     when '63486-1-5000' 'elements wa'     when '63476-1-5000' 'elements wa'     when '63486-1-6000' 'elements wa'     when '63495-1-5000' 'elements wa'     when '63487-1-5000' 'elements wa'     when '63487-1-6000' 'elements wa'     when '63530-1-5000' 'indulgence'     when '63536-1-5000' 'indulgence'     when '63534-1-5000' 'indulgence'     when '63532-1-4000' 'indulgence'     when '63532-1-5000' 'indulgence'     when '63528-1-5000' 'indulgence'     when '63531-1-5000' 'indulgence'     when '63537-1-5000' 'indulgence'     when '63535-1-5000' 'indulgence'     when '63533-1-5000' 'indulgence'     when '63529-1-5000' 'indulgence'     when '63559-1-5000' 'ocean sands'     when '63440-1-5000' 'ocean sands'     when '63510-1-4000' 'ocean sands'     when '63510-1-5000' 'ocean sands'     when '63510-1-6000' 'ocean sands'     when '63516-1-5000' 'ocean sands'     when '63518-1-5000' 'ocean sands'     when '63540-1-5000' 'ocean sands'     when '63540-1-6000' 'ocean sands'     when '63430-1-4000' 'ocean sands'     when '63430-1-5000' 'ocean sands'     when '63430-1-6000' 'ocean sands'     when '63526-1-4000' 'ocean sands'     when '63526-1-5000' 'ocean sands'     when '63526-1-6000' 'ocean sands'     when '63559-1-6000' 'ocean sands'     when '63516-1-6000' 'ocean sands'     when '63560-1-5000' 'ocean sands'     when '63441-1-5000' 'ocean sands'     when '63511-1-5000' 'ocean sands'     when '63511-1-6000' 'ocean sands'     when '63517-1-5000' 'ocean sands'     when '63519-1-5000' 'ocean sands'     when '63541-1-5000' 'ocean sands'     when '63431-1-4000' 'ocean sands'     when '63431-1-5000' 'ocean sands'     when '63431-1-6000' 'ocean sands'     when '63527-1-5000' 'ocean sands'     when '63560-1-6000' 'ocean sands'     when '63322-1-5003' 'pioneer'     when '63332-1-5003' 'pioneer'     when '63330-1-5003' 'pioneer'     when '63334-1-5003' 'pioneer'     when '63323-1-5003' 'pioneer'     when '63333-1-5003' 'pioneer'     when '63335-1-5003' 'pioneer'     when '63326-1-4000' 'symmetry'     when '63326-1-5000' 'symmetry'     when '63326-1-6000' 'symmetry'     when '63334-1-4000' 'symmetry'     when '63334-1-5000' 'symmetry'     when '63334-1-6000' 'symmetry'     when '63334-1-5004' 'symmetry'     when '63327-1-4000' 'symmetry'     when '63327-1-5000' 'symmetry'     when '63327-1-6000' 'symmetry'     when '63335-1-5000' 'symmetry'     when '63320-1-5000' 'symmetry'     when '63320-1-6000' 'symmetry'     when '63321-1-5000' 'symmetry'     when '63321-1-6000' 'symmetry'     when '63314-1-5000' 'western earth'     when '63422-1-5000' 'western earth'     when '63312-1-5000' 'western earth'     when '63322-1-5000' 'western earth'     when '63332-1-5000' 'western earth'     when '63330-1-5000' 'western earth'     when '63338-1-4000' 'western earth'     when '63338-1-5000' 'western earth'     when '63338-1-6000' 'western earth'     when '63422-1-4000' 'western earth'     when '63315-1-5000' 'western earth'     when '63423-1-5000' 'western earth'     when '63313-1-5000' 'western earth'     when '63323-1-5000' 'western earth'     when '63333-1-5000' 'western earth'     when '63339-1-4000' 'western earth'     when '63339-1-5000' 'western earth'     when '63339-1-6000' 'western earth'     when '63318-1-6000' 'western earth'     when '63318-1-5000' 'western earth'     when '63319-1-6000' 'western earth'     when '63319-1-5000' 'western earth'     else '0'     end prodgrp,     sum(case when salesstat.int_num = 32 product.cubic_qty*(salesstat.order_qty-salesstat.return_qty) /1000 else null end) pre11,     sum(case when salesstat.int_num = 33 product.cubic_qty*(salesstat.order_qty-salesstat.return_qty) /1000 else null end) pre10,     sum(case when salesstat.int_num = 34 product.cubic_qty*(salesstat.order_qty-salesstat.return_qty) /1000 else null end) pre09,     sum(case when salesstat.int_num = 35 product.cubic_qty*(salesstat.order_qty-salesstat.return_qty) /1000 else null end) pre08,     sum(case when salesstat.int_num = 36 product.cubic_qty*(salesstat.order_qty-salesstat.return_qty) /1000 else null end) pre07,     sum(case when salesstat.int_num = 37 product.cubic_qty*(salesstat.order_qty-salesstat.return_qty) /1000 else null end) pre06,     sum(case when salesstat.int_num = 38 product.cubic_qty*(salesstat.order_qty-salesstat.return_qty) /1000 else null end) pre05,     sum(case when salesstat.int_num = 39 product.cubic_qty*(salesstat.order_qty-salesstat.return_qty) /1000 else null end) pre04,     sum(case when salesstat.int_num = 40 product.cubic_qty*(salesstat.order_qty-salesstat.return_qty) /1000 else null end) pre03,     sum(case when salesstat.int_num = 41 product.cubic_qty*(salesstat.order_qty-salesstat.return_qty) /1000 else null end) pre02,     sum(case when salesstat.int_num = 42 product.cubic_qty*(salesstat.order_qty-salesstat.return_qty) /1000 else null end) pre01,     sum(case when salesstat.int_num = 43 product.cubic_qty*(salesstat.order_qty-salesstat.return_qty) /1000 else null end) week01,     sum(case when salesstat.int_num = 44 product.cubic_qty*(salesstat.order_qty-salesstat.return_qty) /1000 else null end) week02,     sum(case when salesstat.int_num = 45 product.cubic_qty*(salesstat.order_qty-salesstat.return_qty) /1000 else null end) week03,     sum(case when salesstat.int_num = 46 product.cubic_qty*(salesstat.order_qty-salesstat.return_qty) /1000 else null end) week04,     sum(case when salesstat.int_num = 47 product.cubic_qty*(salesstat.order_qty-salesstat.return_qty) /1000 else null end) week05,     sum(case when salesstat.int_num = 48 product.cubic_qty*(salesstat.order_qty-salesstat.return_qty) /1000 else null end) week06,     sum(case when salesstat.int_num = 49 product.cubic_qty*(salesstat.order_qty-salesstat.return_qty) /1000 else null end) week07,     sum(case when salesstat.int_num = 50 product.cubic_qty*(salesstat.order_qty-salesstat.return_qty) /1000 else null end) week08,     sum(case when salesstat.int_num = 51 product.cubic_qty*(salesstat.order_qty-salesstat.return_qty) /1000 else null end) week09,     sum(case when salesstat.int_num = 52 product.cubic_qty*(salesstat.order_qty-salesstat.return_qty) /1000 else null end) week10    customer,     salesstat,     warereptdetl,     product,     maingrp   product.part_code = salesstat.part_code , product.part_code in ('63782-1-4000','63782-1-5000','63778-1-4000','63778-1-5000','63780-1-5000','63776-1-4000','63776-1-5000','63772-1-4000','63772-1-5000','63774-1-4000','63774-1-5000','63784-1-5000','63786-1-5000','63774-2-1000','63782-2-1000','63778-2-0200','63772-2-1000','63776-2-1000','67744-1-5000','63770-2-1000','63768-2-0200','63772-5-1000','63774-5-1000','63774-5-3000','63760-2-1000','63748-5-4000','66757-5-1000','63774-5-2000','63494-1-5000','63486-1-5000','63476-1-5000','63486-1-6000','63495-1-5000','63487-1-5000','63487-1-6000','63530-1-5000','63536-1-5000','63534-1-5000','63532-1-4000','63532-1-5000','63528-1-5000','63531-1-5000','63537-1-5000','63535-1-5000','63533-1-5000','63529-1-5000','63559-1-5000','63440-1-5000','63510-1-4000','63510-1-5000','63510-1-6000','63516-1-5000','63518-1-5000','63540-1-5000','63540-1-6000','63430-1-4000','63430-1-5000','63430-1-6000','63526-1-4000','63526-1-5000','63526-1-6000','63559-1-6000','63516-1-6000','63560-1-5000','63441-1-5000','63511-1-5000','63511-1-6000','63517-1-5000','63519-1-5000','63541-1-5000','63431-1-4000','63431-1-5000','63431-1-6000','63527-1-5000','63560-1-6000','63322-1-5003','63332-1-5003','63330-1-5003','63334-1-5003','63323-1-5003','63333-1-5003','63335-1-5003','63326-1-4000','63326-1-5000','63326-1-6000','63334-1-4000','63334-1-5000','63334-1-6000','63334-1-5004','63327-1-4000','63327-1-5000','63327-1-6000','63335-1-5000','63320-1-5000','63320-1-6000','63321-1-5000','63321-1-6000','63314-1-5000','63422-1-5000','63312-1-5000','63322-1-5000','63332-1-5000','63330-1-5000','63338-1-4000','63338-1-5000','63338-1-6000','63422-1-4000','63315-1-5000','63423-1-5000','63313-1-5000','63323-1-5000','63333-1-5000','63339-1-4000','63339-1-5000','63339-1-6000','63318-1-6000','63318-1-5000','63319-1-6000','63319-1-5000') , customer.cust_code = salesstat.cust_code , salesstat.rept_code = '0' , warereptdetl.ware_code = salesstat.ware_code , customer.cust_code in ('91826','22978','91247','48972','91386','91520','101472','98214','122586','108675','130703','120847','101155','96360','12005','34263','101517') , salesstat.year_num = 2015 , salesstat.stat_type_code = 'wly' , salesstat.int_num > 31 , salesstat.ord_ind in ('7','8') , maingrp.maingrp_code = salesstat.maingrp_code group prodgrp 

not sure if understand want...

anyway , here goes option work , considering have read grants.

• this example works collection data type
• you cannot add/substract value collection using standard sql.
  so, need inform set of products once.

create temp table tp01 ( prod char(10) , childs set( varchar(10) not null) ) no log ;  insert tp01 values ( 'p1' , set{"xyz", "abc", "ababa"}); insert tp01 values ( 'p3' , set{"aaa", "123", "999"});  create temp table tp02 ( subprod char(10) ) no log ;  insert tp02 values ("xyz"); insert tp02 values ("abc"); insert tp02 values ("123"); insert tp02 values ("999"); insert tp02 values ("xxx"); insert tp02 values ("aba");  select subprod      , tp01.prod tp02 , outer tp01 tp02.subprod in tp01.childs    subprod   prod xyz           p1         abc           p1         123           p3         999           p3         xxx           <null> aba           <null>