Posted: Wed Nov 30, 2016 11:24 am Post subject: Recursive SQL to convert Table rows into columns
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
Joined: 26 Nov 2002 Posts: 12359 Topics: 75 Location: San Jose
Posted: Wed Nov 30, 2016 1:07 pm Post subject:
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
Joined: 01 Feb 2007 Posts: 1075 Topics: 7 Location: At Home
Posted: Thu Dec 01, 2016 4:56 am Post subject:
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.
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