时间戳

首页 > 解决方案 > SQL Sybase - 如何在同一行显示

sql - SQL Sybase - 如何在同一行显示

问题描述

道歉，因为我真的是 SQL 新手，我正在尝试创建一个代码，该代码将提取同一行中的所有值，在它们各自的标识符（fundno 和基金名称）中。

我想要的是所有值都在同一行，而不是分开。

选择

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

出现的是这样的：

https://imgur.com/a/dqPRQ6X

任何人都可以帮我修复代码吗？任何形式的帮助将非常感激。谢谢！！

标签： sqlsybase

解决方案

你想要聚合。. . 和正确的JOIN语法：

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);

笔记：

  • 切勿在FROM子句中使用逗号。
  • 始终使用正确、明确、标准 JOIN的语法。
  • 使用有意义的表别名（表名的缩写），而不是任意字母。
  • 仅对字符串和日期常量使用单引号，而不是列别名。

推荐阅读