MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Recursive SQL to convert Table rows into columns

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
priya_ranjan
Beginner


Joined: 26 Nov 2016
Posts: 4
Topics: 2

PostPosted: Wed Nov 30, 2016 11:24 am    Post subject: Recursive SQL to convert Table rows into columns Reply with quote

I have a requirement to convert Table rows into columns using recursive SQL.


I/P - Table_name: EmplDept
Columns - Name and Department

Code:

Name                Department
--------------      -----------------
Asim Khan           Edison
Peter Husum         Edison
Bryan Brilivan      Prof
Andy Boey           Prof
Niels Lauge         IT Massemarked
Peter Reetz         Topdanmark

O/P -

Edison          Prof               IT Massemarked        Topdanmark
------------    -----------------  -------------------   --------------
Asim Khan         
Peter Husum         
                 Bryan Brilivan     
                 Andy Boey     
                                    Niels Lauge   
                                                             Peter Reetz
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12357
Topics: 75
Location: San Jose

PostPosted: Wed Nov 30, 2016 1:07 pm    Post subject: Reply with quote

priya_ranjan,

I don't think you can use(I may be wrong) recursive sql for this as you need duplicate names and pad with spaces. However it is a plain sql with padding missing values

Here is an Untested SQL which I think will give you the desired results. I assumed that your name column is 30 bytes in length
Code:

SELECT NAME             AS EDISON                 
      ,CHAR(' ',30)     AS PROF                   
      ,CHAR(' ',30)     AS IT_MASSEMARKED         
      ,CHAR(' ',30)     AS TOPDANMARK             
  FROM EMPLDEPT                                   
 WHERE DEPARTMENT = 'EDISON'                     
 UNION ALL                                       
SELECT CHAR(' ',30)     AS EDISON                 
      ,NAME             AS PROF                   
      ,CHAR(' ',30)     AS IT_MASSEMARKED         
      ,CHAR(' ',30)     AS TOPDANMARK             
  FROM EMPLDEPT                                   
 WHERE DEPARTMENT = 'PROF'                       
 UNION ALL                                       
SELECT CHAR(' ',30)     AS EDISON                 
      ,CHAR(' ',30)     AS PROF                   
      ,NAME             AS IT_MASSEMARKED         
      ,CHAR(' ',30)     AS TOPDANMARK             
  FROM EMPLDEPT                                   
 WHERE DEPARTMENT = 'IT MASSEMARKED'             
 UNION ALL                                       
SELECT CHAR(' ',30)     AS EDISON                 
      ,CHAR(' ',30)     AS PROF                   
      ,CHAR(' ',30)     AS IT_MASSEMARKED         
      ,NAME             AS TOPDANMARK             
  FROM EMPLDEPT                                   
 WHERE DEPARTMENT = 'TOPDANMARK'                 
 ;                                               

_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Nic Clouston
Advanced


Joined: 01 Feb 2007
Posts: 1075
Topics: 7
Location: At Home

PostPosted: Thu Dec 01, 2016 4:56 am    Post subject: Reply with quote

I think this is some compettition as this is the third time that this question has come up. I believe it is possible using CTE (Common Table Expressions). I saw something like it some months (years?) back on the SQLite mailing list. Perhaps Priya should join that and search If it is a competition then I do not think that we should be giving answers: pointers - yes, answers - no.
_________________
Utility and Program control cards are NOT, repeat NOT, JCL.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group