Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2010
    Posts
    32

    Unanswered: Is There a Way to Display Row Data as Columns?

    Hi guys,

    I want my query results to display as columns like a spreadsheet. For example, here is my table:

    Code:
    user id    trans cd  
    
    0214	PRAK	I				
    0214	PRAT	I	A	C		
    0214	PR00	I	A	C	D	
    0214	PR01	I	A	C	D	
    0214	PR02	I	A	C	D	
    0214	PR03	I	A	C	D	
    0214	PR04	I	A	C	D	
    0214	PR05	I	A	C	D	
    0214	PR06	I				
    0214	PR07	I				
    0214	PR08	I	A	C	D	
    0214	PR09	I	A	C	D	
    0214	PR10	I				
    0214	PR11	I				
    0214	PR13	I	A	C	D	
    0214	PR14	I				
    0214	PR15	I	A	C	D	
    0214	PR16	I	A	C	D	
    0214	PR17	I	A	C	D	
    0214	PR18	I	A	C	D	
    0214	PR19	I	A	C	D	
    0214	PR20	I	A	C	D	
    0214	PR23	I	A	C	D	
    0214	PR24	I				
    0214	PR25	I				
    0214	PR27	I				
    0214	PR30	I	A	C	D	
    0214	PR41	I				
    0214	PR42	I	A	C	D	
    0214	PR45	I	A	C	D	Q
    0214	PR46	I				
    0214	PR51	I	A	C	D	Q
    0214	PR52	I	A	C	D	Q
    0214	PR54	I	A	C	D	
    0214	PR55	I				
    0214	PR56	I	A	C	D	Q
    0214	PR57	I	A	C	D	Q
    0214	PR58	I				
    0214	PR59	I				
    0214	PR65	I				
    0214	PR66	I				
    0214	PR67	I	A	C	D	
    0214	PR85	I	A	C	D	Q
    0214	PR91	I				
    0214	PR99	I				
    0417	PR01	I				
    0417	PR02	I				
    0417	PR03	I	A	C	D	
    0417	PR04	I				
    0417	PR05	I	A	C	D	
    0417	PR06	I	A	C	D	Q
    0417	PR07	I				
    0417	PR08	I	A	C	D	
    0417	PR09	I				
    0417	PR10	I				
    0417	PR11	I				
    0417	PR13	I				
    0417	PR14	I				
    0417	PR15	I				
    0417	PR16	I	A	C		
    0417	PR17	I				
    0417	PR18	I				
    0417	PR19	I				
    0417	PR20	I	A	C	D	
    0417	PR23	I				
    0417	PR24	I				
    0417	PR25	I				
    0417	PR27	I				
    0417	PR28	I				
    0417	PR30	I				
    0417	PR31	I	A	C	D	
    0417	PR32	I	A	C	D	
    0417	PR33	I	A	C	D	
    0417	PR34	I				
    0417	PR35	I				
    0417	PR36	I				
    0417	PR37	I				
    0417	PR38	I				
    0417	PR39	I	A	C	D	
    0417	PR40	I	A	C	D


    For example, I want to display this data like this for the PR01 record:


    Code:
               
                 0214	   0417
    PR01      I A C D          I

    0214 and 0417 are user ids and I want a new column for each unique user id. Hope this makes sense. Can anyone help?

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    SQL does not allow you to have an unknown number of column in the result set. What you want to do cannot be done the way you want to do it.

    About the best you can hope for is a result set with 2 columns that looks like this:


    Col1 Col2
    -----------------------------------------------
    PRO1 0214: I A C D 0417: I

    Andy

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    take a look at recursive sql. I believe there was an example similar to this on this website last year.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    take a look at recursive sql. I believe there was an example similar to this on this website last year.
    And search with keyword "XMLGROUP", too.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If you are using DB2 for z/OS(I guessed that possibility, because you are using "DSNP." in another thread.),
    you may be able to use XMLAGG instead of XMLGROUP in DB2 Version 9.1 for z/OS.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •