how to merge to multiple result in one row

Question

How to use this SQL server Statement in Powerbuilder 8 datawindow:

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

When you create a new data window,

You have the following options

1. Quick select
2. SQL Select
3. Query
4. External
5. Stored procedure
6. Web service

For your requirement, you can wrap this code in a stored procedure and use option stored procedure

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

Answer 2

Try putting it in the sql statement for the datawindow. Initially when you choose 'Sql Select' as an option you are presented with a list of tables to choose from. This is 'Graphic Mode'. Select any table and a column from it. Then under the Design menu choose 'Data Source' then 'Convert to Syntax'. This will bring up the SQL for the table you choose. Copy and paste your SQL statement into the window (replacing the existing SQL) then close and save the object.