completely OT: SQL query

[kaeli]

I know this is completely OT, but I don't know where to ask and most of
you here seem to know some SQL.
If anyone knows a good group for this question, point me to it please.


I can't figure out a nice way to get some data I want. I have a
convoluted way with virtual tables, but there has to be something easier
than what I am currently doing, so here goes.

I have a table. For example's sake, I'm showing this with a test table,
not my real table.

Anyway, lets call it tblCar. Among the columns are color, make, model,
and year.
So, we can have rows like

blue, chevy, impala, 1990
red, chevy, impala, 1991
blue, ford, explorer, 2000
green, kia, rio, 2001

and so on.

What I need is a good SQL query to get the following type of result.

| blue | green
-------------------------
chevy 5 15
ford 10 20
kia 3 6


A total of colors, by make.
Any ideas?


--
--
~kaeli~
A midget fortune teller who escapes from prison is a small
medium at large.
http://www.ipwebdesign.net/wildAtHeart
http://www.ipwebdesign.net/kaelisSpace

 

[Evelyn V Stevens]

SQL below; other comments inline.

[...]If anyone knows a good group for this question, point me to it please.

If necessary, try comp.databases.

[...] I have a table. For example's sake, I'm showing this with a test table,
Anyway, lets call it tblCar. Among the columns are color, make, model,
and year. So, we can have rows like

blue, chevy, impala, 1990
red, chevy, impala, 1991
blue, ford, explorer, 2000
green, kia, rio, 2001

and so on.

What I need is a good SQL query to get the following type of result.

| blue | green
-------------------------
chevy 5 15
ford 10 20
kia 3 6

A total of colors, by make.

This works in MS Access 2002:

SELECT DISTINCT t0.make,
(SELECT count(*)
FROM tblCar t1
WHERE t1.make = t0.make
AND t1.color = "blue") AS blue,
(SELECT count(*)
FROM tblCar t2
WHERE t2.make = t0.make
AND t2.color = "green") AS green
FROM tblCar t0;

Add as many comma-separated sub-SELECTs as needed for other colors.

Evelyn

 

[kaeli]

SQL below; other comments inline.

This works in MS Access 2002:

SELECT DISTINCT t0.make,
(SELECT count(*)
FROM tblCar t1
WHERE t1.make = t0.make
AND t1.color = "blue") AS blue,

See, the problem was that I don't know all the colors possible. Turns
out that what I wanted is called a pivot table, cross tabulation, or
crosstab and is not easily done in SQL and it can't be done at all
dynamically in SQL (I use oracle), as the values (e.g. the colors) need
to be hard-coded (a procedure can dynamically write it, but this is very
poor performance-wise). I need a third party tool to do it efficiently.
I'm going to write a java class to take a "cube" recordset from Oracle
and transform it into crosstab output as a table because I can't afford
the third-party stuff.

Thanks!

--