| View previous topic :: View next topic |
| Author |
Message |
yadav2005
Intermediate
Joined: 10 Jan 2005
Posts: 348
Topics: 144
|
 Posted: Tue Mar 06, 2007 5:49 am Post subject: DB2 query for displaying rows into columns |
 |
|
Hai,
I have data in table in the format:
| Code: |
SEQNO MARKS
----- -----
1 10
1 20
1 15
1 40
1 33
|
I want the output in the format as:
| Code: |
SEQNO MARKS
----- -----
1 10
15
20
40
33
|
|
|
| Back to top |
|
 |
Nic Clouston
Advanced
Joined: 01 Feb 2007
Posts: 1075
Topics: 7
Location: At Home
|
| Posted: Tue Mar 06, 2007 6:05 am Post subject: |
|
|
Is that your actual data? What is the reasoning for the sequence in the output (it goes 10,20,15,40,33 on the table but 10,15,20,40,33 on output i.e. 15 and 20 have swapped)
_________________
Utility and Program control cards are NOT, repeat NOT, JCL. |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Tue Mar 06, 2007 6:16 am Post subject: |
|
|
yadav2005,
Look Up the QMF Reporting features.
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
yadav2005
Intermediate
Joined: 10 Jan 2005
Posts: 348
Topics: 144
|
| Posted: Thu Mar 08, 2007 4:34 am Post subject: |
|
|
Thanks Kolusu for your reply.
Can i have the query which will give the output in the form:
|
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
|
| Back to top |
|
|
yadav2005
Intermediate
Joined: 10 Jan 2005
Posts: 348
Topics: 144
|
| Posted: Mon Mar 12, 2007 3:35 am Post subject: |
|
|
Kolusu,
I am unable to get the desired results.
| Code: |
CREATE TABLE TEMP
(
SEQNO SMALLINT,
MARKS SMALLINT
);
INSERT INTO TEMP VALUES (1,1);
INSERT INTO TEMP VALUES (1,2);
INSERT INTO TEMP VALUES (1,3);
INSERT INTO TEMP VALUES (1,4);
SELECT SEQNO
,SUM(CASE WHEN MARKS = 1 THEN MARKS ELSE 0 END) AS M1
,SUM(CASE WHEN MARKS = 2 THEN MARKS ELSE 0 END) AS M2
,SUM(CASE WHEN MARKS = 3 THEN MARKS ELSE 0 END) AS M3
,SUM(CASE WHEN MARKS = 4 THEN MARKS ELSE 0 END) AS M4
FROM TEMP
GROUP BY SEQNO;
|
I am getting the error as below and how to solve this to get the desired output.
| Code: |
---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -401, ERROR: THE OPERANDS OF AN ARITHMETIC OR COMPARISON
OPERATION ARE NOT COMPARABLE
DSNT418I SQLSTATE = 42818 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXOBFC SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 930 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'000003A2' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
---------+---------+---------+---------+---------+---------+---------+---------+
|
|
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Mon Mar 12, 2007 6:57 am Post subject: |
|
|
yadav2005,
what is your version of DB2?
Any way try this
| Code: |
SELECT SEQNO
,SUM(CASE WHEN MARKS = 1 THEN MARKS ELSE INT(0) END) AS M1
,SUM(CASE WHEN MARKS = 2 THEN MARKS ELSE INT(0) END) AS M2
,SUM(CASE WHEN MARKS = 3 THEN MARKS ELSE INT(0) END) AS M3
,SUM(CASE WHEN MARKS = 4 THEN MARKS ELSE INT(0) END) AS M4
FROM TEMP
GROUP BY SEQNO;
|
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
yadav2005
Intermediate
Joined: 10 Jan 2005
Posts: 348
Topics: 144
|
| Posted: Mon Mar 12, 2007 8:10 am Post subject: |
|
|
Kolusu,
Still i get the same error and i use DB2 version 7. Please let me know how to get the desired results. |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Mon Mar 12, 2007 9:56 am Post subject: |
|
|
| yadav2005 wrote: | Kolusu,
Still i get the same error and i use DB2 version 7. Please let me know how to get the desired results. |
There is no way you should get the error. The query works fine. Check and see if there is another table named TEMP where marks is defined as character data type.
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
yadav2005
Intermediate
Joined: 10 Jan 2005
Posts: 348
Topics: 144
|
| Posted: Wed Mar 14, 2007 5:27 am Post subject: |
|
|
Kolusu,
You are really great and you are correct and i am sorry to say that i was refering to a wrong table that is why i was not able to get the dessired results.
Thanks for your answer. |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
