Using Sql queries, is a output like this possible

baidyanath_biswas · New User Joined: 04 Sep 2006 Posts: 11

I have a table like -
TYPE COLOUR
---- --------
R1 YELLOW
R1 YELLOW
R1 RED
R1 BLUE
R1 GREY
R2 WHITE
R2 YELLOW
R2 GREEN
R2 GREY

Using Sql queries, is a output like this possible?

mkk157 · Active User Joined: 17 May 2006 Posts: 310

Hi baidyanath_biswas,

Here u r trying to make the fields into columns. I believe it is not possible.

vini_srcna · Posted: Thu Mar 22, 2007 9:37 pm

Think you have to do RND on this.

Prajesh_v_p · Active User Joined: 24 May 2006 Posts: 133 Location: India

We used do these using QMF query and a QMF form. I think u can do this using the USAGE in a QMF form.

baidyanath_biswas · New User Joined: 04 Sep 2006 Posts: 11

Any help guys?? Actually even if i want to search in the net for this particular thing,what to give as keyword to search? i donno

dick scherrer · Posted: Fri Mar 23, 2007 8:03 pm

Hello,

If you want this quickly, it is a simple matter to write a program that retrieves the rows and builds a 2-dimensional array using the "type" and "color" for the dimensions.

Once the rows have all been passed and the counts accumulated, the last bit of code would start the "top" of the array and present the grid of totals.

Alan Voss · Posted: Fri Mar 23, 2007 10:08 pm

Perhaps this is ugly, and it only works if you know all possible values for COLOUR before hand:
select type,
sum (case colour
when 'YELLOW' then 1 else 0 end) as Yellow,
sum (case colour
when 'RED' then 1 else 0 end) as RED,
sum (case colour
when 'BLUE' then 1 else 0 end) as BLUE,
sum (case colour
when 'GREY' then 1 else 0 end) as GREY,
sum (case colour
when 'WHITE' then 1 else 0 end) as WHITE,
sum (case colour
when 'GREEN' then 1 else 0 end) as GREEN
from table
group by type
order by type;