Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
All names in a dept in one row

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Bharath Nadipally

New User


Joined: 24 Jun 2008
Posts: 22
Location: Hyderabad

PostPosted: Sun Mar 29, 2009 5:09 pm    Post subject: All names in a dept in one row
Reply with quote

Hi,
Below are the records in a table.

Dept name
------ -------
10 n1
10 n2
10 n3
20 n4
20 n5

required o/p:
10 n1,n2,n3
20 n4,n5

Is it possible to get required output using one sql query?
Back to top
View user's profile Send private message

kregen

New User


Joined: 16 Mar 2006
Posts: 18

PostPosted: Mon Mar 30, 2009 2:21 pm    Post subject: Reply to: All names in a dept in one row
Reply with quote

hi Bharath Nadipally,

Tabledefinition
Code:
  DECLARE GLOBAL TEMPORARY TABLE SESSION.DEPTTMP 
    (DEPT    SMALLINT NOT NULL                   
    ,NAME    CHAR(5)  NOT NULL                   
    )                                             
  ;                                               
                                                 


Testdata
Code:

  INSERT INTO SESSION.DEPTTMP VALUES(10, 'N1');   
  INSERT INTO SESSION.DEPTTMP VALUES(10, 'N2');   
  INSERT INTO SESSION.DEPTTMP VALUES(10, 'N3');   
  INSERT INTO SESSION.DEPTTMP VALUES(10, 'N4');   
  INSERT INTO SESSION.DEPTTMP VALUES(10, 'N5');   
  INSERT INTO SESSION.DEPTTMP VALUES(10, 'N6');   
                                                 
  INSERT INTO SESSION.DEPTTMP VALUES(20, 'N1');   
  INSERT INTO SESSION.DEPTTMP VALUES(20, 'N2');   
  INSERT INTO SESSION.DEPTTMP VALUES(20, 'N3');   
                                                 
  INSERT INTO SESSION.DEPTTMP VALUES(30, 'N4');   
  INSERT INTO SESSION.DEPTTMP VALUES(30, 'N5');   
  INSERT INTO SESSION.DEPTTMP VALUES(30, 'N6');


And now the SQL-Statment
Code:

 WITH TEMP1 (DEPT, W#, NAME, ALL_NAME) AS             
      (SELECT DEPT                                     
             ,SMALLINT(1)                             
             ,MIN(NAME)                               
             ,VARCHAR(MIN(STRIP(NAME,B,' ')), 4096)   
         FROM SESSION.DEPTTMP A                       
        GROUP BY DEPT                                 
      UNION ALL                                       
        SELECT A.DEPT                                 
              ,SMALLINT(B.W#+1)                       
              ,A.NAME                                 
              ,B.ALL_NAME || ',' || STRIP(A.NAME,B,' ')
          FROM SESSION.DEPTTMP A                       
              ,TEMP1 B                                 
         WHERE A.DEPT = B.DEPT                         
           AND A.NAME > B.NAME                         
           AND A.NAME = (SELECT MIN(C.NAME)           
                           FROM SESSION.DEPTTMP C     
                          WHERE C.DEPT = B.DEPT       
                            AND C.NAME > B.NAME)       
      )                                               
 SELECT DEPT                                           
       ,ALL_NAME                                       
   FROM TEMP1 D                                       
    WHERE W# = (SELECT MAX(W#)                         
                  FROM TEMP1 E                         
                 WHERE D.DEPT = E.DEPT)               
  ORDER BY DEPT;                                       


a little bit longer... but one statment

Warning: this statment is very slow! don't use it when you have a big table with many rows

cu
kregen
Back to top
View user's profile Send private message
Bharath Nadipally

New User


Joined: 24 Jun 2008
Posts: 22
Location: Hyderabad

PostPosted: Tue Mar 31, 2009 10:35 am    Post subject: Reply to: All names in a dept in one row
Reply with quote

hi kregen,

Thank you very much.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts RFE: DB2 support for mixed case names. Pedro DB2 0 Tue Jul 04, 2017 1:32 am
No new posts Variable for cursor names ravikumar15 DB2 5 Sun Jul 17, 2016 7:08 am
No new posts Using Field names in FILEAID Batch CO... Atul Banke Compuware & Other Tools 2 Wed Jun 29, 2016 9:54 pm
No new posts Utility to List PDS member names with... GaganGarg CLIST & REXX 6 Fri Jun 12, 2015 12:25 pm
No new posts Using Wildcard for PDS names with PDS... PokerGuru CA Products 1 Fri May 22, 2015 2:30 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us