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: 1788
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: 1228
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: 1696
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: 1788
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 unload data from table with lob columns farhad_evan DB2 0 Sat Apr 22, 2017 1:32 pm
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts To display values of multiple rows in... vinu78 DB2 2 Thu Mar 09, 2017 1:20 pm
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm


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