This is how I have always done it and currently do it using SQLite. Not sure
if this is standard, but some DBMS' I have encountered support an
'immediate-if' type function (on Oracle it's called DECODE, on Access and
Foxpro, it's slightly different and is called IIF). This function can be
used to do this transpose type operation by using an aggregate query, ie,
let's say you have a table like the original poster's with names and phone
numbers. We'll simplify it to a single table with name, phone type and phone
number. Let's say phone types are A, B and C, and for each phone number a
person has, they have one entry in this table:
Andrew A 000-0000
Andrew B 111-1111
Andrew C 222-2222
So to get the desired result, on Oracle you would do:
SELECT name, max(decode(type,'A',phone)) as phoneA,
max(decode(type,'B',phone)) as phoneB,
max(decode(type,'C',phone)) as phoneC
FROM phones group by name
I implement a function called equalcheck for SQLite using the
sqlite_create_function. This function takes 3 parameters, it returns the
value of the third parameter if the first two are equal, otherwise it
returns null:
void equalCheck(sqlite_func* fn,int nump,const char**parameters)
{
if(!strcmp(parameters[0], parameters[1])) {
sqlite_set_result_string(fn,parameters[2],-1);
} else {
sqlite_set_result_string(fn,0,0);
}
}
Then I can execute the following statement to get the results, which in the
original poster's database would be one row for each person, and phones
broken out into columns:
select name,
max(equalcheck(type,'A',phone)) as aphone,
max(equalcheck(type,'B',phone)) as bphone,
max(equalcheck(type,'C',phone)) as cphone
from phones group by name
So my suggestion would be to add the equivalent of the Oracle DECODE
function to the list of supported functions, since it would facilitate this
type of transpose operation.
-----Original Message-----
From: D. Richard Hipp [mailto:[EMAIL PROTECTED]
Sent: Monday, December 22, 2003 4:27 PM
To: [EMAIL PROTECTED]
Subject: Re: [sqlite] How do I coalesce values in a join?
Scott Lamb wrote:
> Ron Aaron wrote:
>
>> I have two tables:
>> people (id,first,last)
>>
>> and phones (id, type, phone)
>
>
> ...
>
>> What I would like to do, is 'coalesce' the phones to produce another
>> table:
>>
>> peopleandphones (id,first,last,home,cel,work,fax)
>>
>> So I could make a much nicer report. But I cannot figure out how to
>> do this!
>
>
> Raw SQL doesn't really do that well.
Can anybody suggest some kind of extended SQL syntax that
could be added to SQLite that would make this kind of thing easier?
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
