From: Frank Bax <fbax@sympatico.ca>
To: pgsql-sql@postgresql.org
Subject: Re: combining records from a single table and presenting
Date: Thu, 27 Oct 2005 19:37:12 -0400
Message-ID: <5.2.1.1.0.20051027192214.04d05490@pop6.sympatico.ca> (raw)
In-Reply-To: <506ca6630510271100y367e1fb2hfb05f5ecaae043a3@mail.gmail.co m>
At 02:00 PM 10/27/05, Abhishek wrote:
>I have a table "TABLE1" which has
>----------------------------------------------------
>Callguid | digits | type
>----------------------------------------------------
>xxxx 123 'a'
>xxxx 345 'b'
>xxxx 678 'c'
>
>type can have only 'a', 'b' or 'c' as its value.
>I am tryng to write a query which returns me a record like this
>
>-------------------------------------------------------------------------------------------------------
>CallGuid | a type digits | b type digits | c
>type digits
>-------------------------------------------------------------------------------------------------------
>xxxx 123 345
> 678
1) You didn't say if 'a','b','c' records always exist for every callguid.
2) You didn't say if there is more than one record for a given callguid/type.
If (1) is 'yes' and (2) is 'no'
select a.callguid, a.digits as a_digits, b.digits as b_digits, c.digits as
c_digits
from
(select callguid,digits from table1 where type='a') as a
join
(select callguid,digits from table1 where type='b') as b on
a.callguid=b.callguid
join
(select callguid,digits from table1 where type='c') as c on
a.callguid=c.callguid;
If (1) is 'no' and (2) is 'no'
select coalesce(a.callguid,b.callguid,c.callguid) as callguid,
a.digits as a_digits, b.digits as b_digits, c.digits as c_digits
from
(select callguid,digits from table1 where type='a') as a
full outer join
(select callguid,digits from table1 where type='b') as b on
a.callguid=b.callguid
full outer join
(select callguid,digits from table1 where type='c') as c on
a.callguid=c.callguid;
If (2) is 'yes', you're on your own. You can also try searching for
"crosstab" and/or "pivot table" for more info.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: fbax@sympatico.ca
Subject: Re: combining records from a single table and presenting
In-Reply-To: <5.2.1.1.0.20051027192214.04d05490@pop6.sympatico.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox