IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Db2 SQL query to convert rows into columns


IBM Mainframe Forums -> DB2
Post new topic   This topic is locked: you cannot edit posts or make replies.
View previous topic :: View next topic  
Author Message
subratarec

Active User


Joined: 22 Dec 2007
Posts: 126
Location: Bangalore

PostPosted: Thu Nov 17, 2016 4:51 pm
Reply with quote

Hi All,

Need your help/suggestion on below query. Could someone please share his/her thought on how to create below query..

Table Name: My_Dept

Db2 Table
--------------------------------------------
Code:
Emp_Name | Dept_name
-----------------------------
AAAA     | Math
-----------------------------
BBBB     | Math
-----------------------------
CCCC     | Physics
-----------------------------
DDDD     | Chemistry
-----------------------------
EEEE     | Physics
-----------------------------
FFFF     |  Physics     

output of query result..


Code:
Math | Physics | Chemistry
------------------------------
AAAA |           |
------------------------------
BBBB |           |
------------------------------
     | CCCC      |
------------------------------
     | EEEE      |
------------------------------
     | FFFF      |
------------------------------
     |           | DDDD


Thanks
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Thu Nov 17, 2016 7:04 pm
Reply with quote

This same question has been asked on the beginners forum, I suggest yoou go there and follow that topic. To get there, if you do not already know it, use the Protal link at the top of every page in ths forum and on the left there is a section for beginners with 3 links including a link to that forum.

If that was your topic then posting again on this forum is a waste of everyones time and is bad internet manners.
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1353
Location: Israel

PostPosted: Thu Nov 17, 2016 7:17 pm
Reply with quote

If the number of "Dept_name" is fixed and known, then you could write:
Code:
SELECT                               
   CASE DEPT_NAME
      WHEN 'Math' THEN Emp_Name
      ELSE ' '                       
   END AS MATH,                               
   CASE DEPT_NAME
      WHEN 'Physics' THEN Emp_Name
      ELSE ' '                       
   END AS PHYSICS,                               
   CASE DEPT_NAME
      WHEN 'Chemistry' THEN Emp_Name
      ELSE ' '                       
   END as CHEMISTRY                               
FROM                                 
   My_Dept
ORDER BY
   DEPT_NAME

However, this kind of coding is absolutely not recommended: a new department would requires changes in the query.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Thu Nov 17, 2016 10:06 pm
Reply with quote

Quote:
However, this kind of coding is absolutely not recommended: a new department would requires changes in the query.
This is the optimal solution which needs minimal change.
Back to top
View user's profile Send private message
subratarec

Active User


Joined: 22 Dec 2007
Posts: 126
Location: Bangalore

PostPosted: Fri Nov 18, 2016 9:12 am
Reply with quote

Hi Marso,

First of thanks a lot!! for spending time on my query and helping me out with your post. There is one BIG mistake I made while posting the query. I was in a hurry to post the query and it's completely my fault that I missed the main point while posting the query.

The requirement will remain the same but with one small change and that is I need to create Recursive SQL to achieve the same result.

Frankly I have never done Recursive SQL query so if anyone knows the same please let me know. I have started reading on this topic and still I don't have much clear idea on the same.

Again I apologize for the inconvenience and it's completely my fault that missed the main point (DB2 Recursive SQL) while posting the topic.

Thanks
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Fri Nov 18, 2016 4:59 pm
Reply with quote

Well, you are a beginner re recursive SQL so I suggest you , and others, reference the same topic in the beginners forum.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   This topic is locked: you cannot edit posts or make replies. View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts To get the count of rows for every 1 ... DB2 3
No new posts RC query -Time column CA Products 3
No new posts Need to convert date format DFSORT/ICETOOL 20
No new posts Remote Unload of CLOB Columns DB2 6
No new posts Increase the number of columns in the... IBM Tools 3
Search our Forums:

Back to Top