Page 1 of 1

Extracting Multiple rows as columns of single row

PostPosted: Tue May 01, 2018 6:43 am
by madlaks
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
ID  ACCT  PROD ID
1   1234  P1
1   1234  P3


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

Expected Result
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.

Re: Extracting Multiple rows as columns of single row

PostPosted: Mon Aug 13, 2018 4:44 pm
by alexm
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 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

Re: Extracting Multiple rows as columns of single row

PostPosted: Mon Aug 13, 2018 9:15 pm
by alexm
Another solution would be using a common table expression and an OLAP function:


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