Re: combining records from a single table and presenting

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.digitsas 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.