SQL Sybase – How to display on the same row

Apologies as I’m really new to SQL, and I’m trying to create a code that would extract all values in the same row, in their same respective identifiers (fundno and fund name).

What I want is all the values are in the same row, and not separated.

select

b.fundno,
a.fund_name,
a.fund_type As Type,
a.fund_status_code As Status,
a.category_code as 'CatCode',
convert(char(10),b.prosp_date,101)as 'Prospectus_date',
    (case when b.prosp_code = 'GRMFEE' then b.prosp_value end) as GrMgmt,
    (case when b.prosp_code = 'GR12B1' then b.prosp_value end) as Gr12b1,
    (case when b.prosp_code = 'GROTHR' then b.prosp_value end) as Groth,
    (case when b.prosp_code = 'GREXP' then b.prosp_value end) as Grtotal,
    (case when b.prosp_code = 'NETMFEE' then b.prosp_value end) as Netmgmt,
    (case when b.prosp_code = 'NET12B1' then b.prosp_value end) as Net12b1,
    (case when b.prosp_code = 'NETOTHR' then b.prosp_value end) as Netoth,
    (case when b.prosp_code = 'EXPLIMIT' then b.prosp_value end) as Nettotal,
    (case when b.prosp_code = 'NETWVR' then b.prosp_value end) as Waiver

from 
fund a,
prospectus_breakdown b

where a.category_code in(1,13,16,18,19,20,27,31)

and b.fundno = a.fundno


and b.prosp_date=(select max(prosp_date)
                            from prospectus_breakdown b
            where b.fundno=a.fundno)

order by a.fund_name

What appears is this:

https://imgur.com/a/dqPRQ6X

Can anyone please help me fix the code? Any kind of help would be really appreciated. Thank you!!

Answer

You want aggregation . . . and proper JOIN syntax:

select f.fundno, f.fund_name, f.fund_type As Type,
       f.fund_status_code As Status,
       f.category_code as CatCode,
       convert(char(10), pb.prosp_date, 101) as Prospectus_date,
       max(case when pb.prosp_code = 'GRMFEE' then pb.prosp_value end) as GrMgmt,
       max(case when pb.prosp_code = 'GR12B1' then pb.prosp_value end) as Gr12b1,
       max(case when pb.prosp_code = 'GROTHR' then pb.prosp_value end) as Groth,
       max(case when pb.prosp_code = 'GREXP' then pb.prosp_value end) as Grtotal,
       max(case when pb.prosp_code = 'NETMFEE' then pb.prosp_value end) as Netmgmt,
       max(case when pb.prosp_code = 'NET12B1' then pb.prosp_value end) as Net12b1,
       max(case when pb.prosp_code = 'NETOTHR' then pb.prosp_value end) as Netoth,
       max(case when pb.prosp_code = 'EXPLIMIT' then pb.prosp_value end) as Nettotal,
       max(case when pb.prosp_code = 'NETWVR' then pb.prosp_value end) as Waiver    
from fund f join
     prospectus_breakdown pb
     on f.fundno = pb.fundo
where f.category_code in (1, 13, 16, 18, 19, 20, 27, 31)
group by f.fundno, f.fund_name, f.fund_type,
       f.fund_status_code, f.category_code,
       convert(char(10), pb.prosp_date, 101);

Notes:

• Never use commas in the FROM clause.
• Always use proper, explicit, standard JOIN syntax.
• Use meaningful table aliases (abbreviations for table names), rather than arbitrary letters.
• Only use single quotes for string and date constants, not for column aliases.