CODEWITHSUNDEEP
sqlinformix
Alexander Baltasar
Alexander Baltasar

Reputation: 1064

Informix Select and output result as a matrix

I have a table called "sales_pos" with following columns:

cust_nr ; cust_name ; cust_ans ; date ; month_year ; value_goods

Example:

1234;Jon Doe;New York;31/01/2015;1/2015;250,00
4711;Max Muster;New York;22/03/2015;01/2015;900,00
0812;Will Smith;New York;22/02/2015;01/2015;300,00
1234;Jon Doe;New York;11/01/2015;1/2015;150,00

I want a selec with a result as follows:

Customer                 |1/2015|2/2015|3/2015|4/2015| .. |12/2015|
0812 Will Smith New York |300,00|..    |..    |..    | .. |..     | 
1234 Jon Doe New York    |400,00|..    |..    |..    | .. |..     |  
4711 Max Muster New York |..    |..    |900,00|..

..

Select 
  cust_nr, cust_name, cust_ans, month_year, sum(value_goods)
from sales_pos
group by 
  cust_nr, cust_name, cust_ans, month_year

This select-statement has all information i need but I don't know how to transform this result into the matrix above.

I also tried:

select
  cust_nr, cust_name, cust_ans, month_year, sum(value_goods)
from sales_pos
where
  month_year = '1/2015'
group by cust_nr, cust_name, cust_ans
UNION ALL
select
  cust_nr, cust_name, cust_ans, month_year, sum(value_goods)
from sales_pos
where
  month_year = '2/2015'
group by cust_nr, cust_name, cust_ans
UNION ALL
select
  cust_nr, cust_name, cust_ans, month_year, sum(value_goods)
from sales_pos
where
  month_year = '3/2015'
group by cust_nr, cust_name, cust_ans
UNION ALL
...

But this did not work either. I hope somebody can help. THX

Upvotes: 0

Views: 89

Answers (2)

Alexander Baltasar
Alexander Baltasar

Reputation: 1064

I finaly found a working solution for informix 7.5

select DISTINCT 
   CUST.cust_nr, CUST.cust_name, CUST.cust_ans, 
   (select sum(M01.value_goods) from $table as M01 where M01.month_year = '1/2015' and M01.cust_nr = CUST.cust_nr),
   (select sum(M02.value_goods) from $table as M02 where M02.month_year = '2/2015' and M02.cust_nr = CUST.cust_nr),
   ...
   (select sum(M12.value_goods) from $table as M12 where M12.month_year = '12/2015' and M12.cust_nr = CUST.cust_nr)
from sales_pos as CUST order by CUST.cust_nr;

I don't like this solution, but at least it works :-(

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270633

If you know what the headers are, you can use conditional aggregation:

Select cust_nr, cust_name, cust_ans,
       sum(case when month_year = '1/2015' then value_goods end) as MY_012015,
       sum(case when month_year = '2/2015' then value_goods end) as MY_022015,
       . . .
       sum(case when month_year = '12/2015' then value_goods end) as MY_122015
from sales_pos
group by  cust_nr, cust_name, cust_ans;

Upvotes: 1

Related Questions