IBM Mainframe Forum

by **madlaks** » Tue May 01, 2018 6:43 am

I have 2 Tables like below and trying to extract 1 record per account by joining 2 tables. I retrieved as multiple rows, but would like to extract as 1 record

Table1

Select all

ID  ACCT  PROD ID
1   1234  P1
1   1234  P3

Table2

Select all

Prod ID  Prod Name
P1       Product 1
P2       Product 2
P3       Product 3

Expected Result

Select all

1234, Product 1, Product 3

Is it possible to extract in single query. I tried CASE WHEN but couldn't figure it out

Use the code tags when presenting data. Coded for you this time.

by **alexm** » Mon Aug 13, 2018 4:44 pm

Hi,
an answer is open since 3 month

- for the case you're still interested...

Beside other solutions, you may use a combination of DB2's XML aggregate and scalar built-in functions to do the trick, as follows:

Select all

SELECT T1.ACCT                                        
      ,STRIP(                                         
        STRIP(                                        
         CAST(                                        
          XMLSERIALIZE(                               
           XMLAGG(                                    
            XMLTEXT( STRIP(T2.PROD_NAME) CONCAT ', ' )
           ORDER BY T2.PROD_NAME )                    
          AS CLOB(80) EXCLUDING XMLDECLARATION )      
         AS CHAR(80) )                                
        ,T,' ' )                                      
       ,T,',' ) AS RESULT                             
FROM   TAB1 T1                                        
JOIN   TAB2 T2                                        
 ON    T2.PROD_ID = T1.PROD_ID                        
GROUP  BY T1.ACCT                                     
;                                                     
---------+---------+---------+---------+---------+----
  ACCT  RESULT                                        
---------+---------+---------+---------+---------+----
  1234  PRODUCT 1, PRODUCT 3                          
 

Best regards,
alexm

by **alexm** » Mon Aug 13, 2018 9:15 pm

Another solution would be using a common table expression and an OLAP function:

Select all

WITH   RAW (ACCT ,PROD_NAME ,SEQ)                   
AS   ( SELECT T1.ACCT                               
             ,T2.PROD_NAME                          
             ,ROW_NUMBER() OVER(PARTITION BY T1.ACCT
                                ORDER BY T1.PROD_ID)
       FROM   TAB1 T1                               
       JOIN   TAB2 T2                               
        ON    T2.PROD_ID = T1.PROD_ID               
     )                                              
SELECT R1.ACCT                                      
      ,COALESCE(R1.PROD_NAME,' ') AS FIRST          
      ,COALESCE(R2.PROD_NAME,' ') AS SECOND         
      ,COALESCE(R3.PROD_NAME,' ') AS THIRD          
FROM   RAW R1                                       
LEFT   OUTER JOIN RAW R2                            
       ON R2.ACCT = R1.ACCT                         
       AND R2.SEQ = 2                               
LEFT   OUTER JOIN RAW R3                            
       ON R3.ACCT = R1.ACCT                         
       AND R3.SEQ = 3                               
WHERE  R1.SEQ = 1                                   
;                                                   
---------+---------+---------+---------+---------+--
  ACCT  FIRST      SECOND     THIRD                 
---------+---------+---------+---------+---------+--
  1234  PRODUCT 1  PRODUCT 3                        
  4321  PRODUCT A  PRODUCT B  PRODUCT C             
 

Please note:
- Depending on the design and size of your table(s) this query might become a cost issue.
- You need to know the maximum number of projects that can be assigned to an account, and repeat the queries LEFT OUTER JOINs accordingly.
- For this example, I've added some more data to the given setup (ID=2, ACCT=4321, PROD_ID/NAME=PA, PB, PC).

IBM Mainframe Forum

Extracting Multiple rows as columns of single row

Extracting Multiple rows as columns of single row

Re: Extracting Multiple rows as columns of single row

Re: Extracting Multiple rows as columns of single row