|
View previous topic :: View next topic
|
| Author |
Message |
itdsen
New User
Joined: 20 Sep 2006
Posts: 23
Location: Chennai
|
|
|
|
Hi,
there are two tables table1 & table2
Table 1
no name address
1 xxxx ..........
1 zzzzz mmmm
2 sssss vvvvvv
4 aaaa qqqqqq
..............
......... so on
Table 2
no price s.no
1 100 1
1 200 2
1 000 3
1 210 4
2 000 1
2 110 2
3 100 1
4 160 3
4 100 1
...... so on
i need result as like
no name price price price price
when s.no=1 s.no=2 s.no=3 s.no=4
1 xxxx 100 200 000 210
1 zzzz 100 200 000 210
2 ssss 000 110 000 000
4 aaaa 100 000 160 000
...... so on |
|
| Back to top |
|
 |
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
| This is not possible ...you cant mix and match different rows ...why dont you try this out in your program using stringing functions ? |
|
| Back to top |
|
|
Amsar
New User
.jpg)
Joined: 26 Sep 2008
Posts: 84
Location: Chennai
|
|
|
|
| Quote: |
| This is not possible |
I think it is 
I just tried with some sample and it's working for me.
| Code: |
TAB1
----
NO
NAME
ADDRESS
TAB2
----
NO
PRICE
SRNO |
| Code: |
SELECT NO,NAME,
MAX(CASE WHEN SRNO = 1 THEN PRICE ELSE 0 END),
MAX(CASE WHEN SRNO = 2 THEN PRICE ELSE 0 END),
MAX(CASE WHEN SRNO = 3 THEN PRICE ELSE 0 END),
MAX(CASE WHEN SRNO = 4 THEN PRICE ELSE 0 END)
FROM
(
SELECT A.NO, NAME, PRICE, SRNO FROM
TAB1 A,
TAB2 B
WHERE A.NO = B.NO
) T1
GROUP BY NO,NAME; |
OUTPUT
| Code: |
---------+---------+---------+---------+---------+---------+-----
NO NAME
---------+---------+---------+---------+---------+---------+-----
1 xxxx 100 200 000 210
1 zzzzz 100 200 000 210
2 sssss 000 110 000 000
4 aaaa 100 000 160 000
DSNE610I NUMBER OF ROWS DISPLAYED IS 4
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100 |
|
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
it works because You set a limit on the number of report columns to be filled!
for the more general case of unknown number of report columns you will have to write a program |
|
| Back to top |
|
|
Amsar
New User
Joined: 26 Sep 2008
Posts: 84
Location: Chennai
|
|
|
|
| itdsen wrote: |
Hi,
i need result as like
no name price price price price
when s.no=1 s.no=2 s.no=3 s.no=4
1 xxxx 100 200 000 210
1 zzzz 100 200 000 210
2 ssss 000 110 000 000
4 aaaa 100 000 160 000
...... so on |
enrico,
Yes it is limited to 4. But the expected results given by the OP does n't say anything about an extra column like when s.no=5 or greater. I guess now it's upto him to comeback on this. |
|
| Back to top |
|
|
itdsen
New User
Joined: 20 Sep 2006
Posts: 23
Location: Chennai
|
|
|
|
Thanks Amsar :D :D
Query is working fine. |
|
| Back to top |
|
|
|
|
