如何将多个结果合并到一行中

Question

如何在Powerbuilder 8数据窗口中使用此SQL server语句：

SELECT t1.floor_code,  
unit_code=STUFF  
(  
(  
SELECT DISTINCT '-' + CAST(t2.unit_code AS VARCHAR(MAX))  
FROM Table2 t2   
WHERE t2.company_code = t1.company_code and
t2.office_code  = t1.office_code and
t2.ps_contract_hdr_code = t1.ps_contract_hdr_code and
t2.floor_code = t1.floor_code   
FOR XML PATH('')  
),1,1,''  
)  
FROM Table1 t1  
GROUP BY company_code, floor_code  , office_code, ps_contract_hdr_code
Order BY company_code, floor_code  , office_code, ps_contract_hdr_code

Answer 1

尝试将其放入数据窗口的sql语句中。最初，当您选择"Sql Select"作为选项时，您会看到一个可供选择的表列表。这是"图形模式"。从中选择任何表和列。然后在"设计"菜单下选择"数据源"，然后选择"转换为语法"。这将为您选择的表显示SQL。将SQL语句复制并粘贴到窗口中(替换现有的SQL(，然后关闭并保存对象。

Answer 2

当您创建一个新的数据窗口时，

您有以下选项

1. 快速选择
2. SQL选择
3. 查询
4. 外部
5. 存储过程
6. Web服务

根据您的要求，您可以将此代码包装在存储过程中，并使用选项存储过程

CREATE PROCEDURE test
as
BEGIN
SELECT t1.floor_code,
unit_code = STUFF((SELECT DISTINCT 
'-'+CAST(t2.unit_code AS VARCHAR(MAX))
FROM Table2 t2
WHERE t2.company_code = t1.company_code
AND t2.office_code = t1.office_code
AND t2.ps_contract_hdr_code = t1.ps_contract_hdr_code
AND t2.floor_code = t1.floor_code FOR XML PATH('')
),1,1,'')
FROM Table1 t1
GROUP BY company_code,
floor_code,
office_code,
ps_contract_hdr_code
ORDER BY company_code,
floor_code,
office_code,
ps_contract_hdr_code
END