Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Db2 SQL query to convert rows into columns

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

New User


Joined: 22 Dec 2007
Posts: 73
Location: Bangalore

PostPosted: Thu Nov 17, 2016 4:51 pm    Post subject: Db2 SQL query to convert rows into columns
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: 1715
Location: UK

PostPosted: Thu Nov 17, 2016 7:04 pm    Post subject:
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: 1219
Location: Israel

PostPosted: Thu Nov 17, 2016 7:17 pm    Post subject:
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

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Thu Nov 17, 2016 10:06 pm    Post subject:
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

New User


Joined: 22 Dec 2007
Posts: 73
Location: Bangalore

PostPosted: Fri Nov 18, 2016 9:12 am    Post subject: Reply to: Db2 SQL query to convert rows into columns
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: 1715
Location: UK

PostPosted: Fri Nov 18, 2016 4:59 pm    Post subject:
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.    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts How to convert the VBM file to VB or... Sulabh Agrawal JCL & VSAM 4 Fri Nov 18, 2016 1:04 pm
No new posts Convert +9999999999999.99 to S9(12)V9(3) vnktrrd DFSORT/ICETOOL 8 Thu Nov 17, 2016 8:15 pm
No new posts convert PD to ZD ram_vizag DFSORT/ICETOOL 5 Sat Nov 05, 2016 3:06 am
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Row-Numbers of distinct rows? Auryn DB2 1 Thu Oct 20, 2016 4:38 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us