Recursive SQL to convert Table rows into columns
Select messages from
# through # FAQ
[/[Print]\]

MVSFORUMS.com -> Database

#1: Recursive SQL to convert Table rows into columns Author: priya_ranjan PostPosted: Wed Nov 30, 2016 11:24 am
    —
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

#2:  Author: kolusuLocation: San Jose PostPosted: Wed Nov 30, 2016 1:07 pm
    —
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'                 
 ;                                               

#3:  Author: Nic CloustonLocation: At Home PostPosted: Thu Dec 01, 2016 4:56 am
    —
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.



MVSFORUMS.com -> Database


output generated using printer-friendly topic mod. All times are GMT - 5 Hours

Page 1 of 1

Powered by phpBB © 2001, 2005 phpBB Group