View previous topic :: View next topic
|
Author |
Message |
subratarec
Active User
Joined: 22 Dec 2007 Posts: 126 Location: Bangalore
|
|
|
|
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 |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
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 |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006 Posts: 1353 Location: Israel
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3051 Location: NYC,USA
|
|
|
|
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 |
|
|
subratarec
Active User
Joined: 22 Dec 2007 Posts: 126 Location: Bangalore
|
|
|
|
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 |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
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 |
|
|
|