Need a solution for this sql query

Question

Need a solution for this sql query

Guvera

SSC-Addicted

Points: 426
More actions
January 21, 2011 at 2:18 am

#388682

Hi,
I am using the below query in my sql. Here is four table. I join the table and i show that what output i need.
Select Reqid,Name,Status,E.ProPName
from ReqMaster R join ProjMaster P on R.Id=P.id join Systext S on S.sid=P.sid
join ExtDef D on S.ExtId=E.ExtID
this returns
multiple rows for sing req id with different value for property like
Reqid Name Status ProppName
1 hhhh jjj klkkj
1 hhhh jjj kjgfff
1 hhhh jjj nnnnnn
but i want the result as
Reqid Name Status PropName1 PropName2 PropName3
1 hhhh jjj klkkj kjgfff nnnnnn
Hope awaiting for your's reply guys.
Regards
Guvera

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

Lookup the PIVOT function. You'll have to create a column in the table/result set that the PIVOT will work from that will give names to each row though. Shouldn't be too hard, just append a ROW_NUMBER() function to 'PROC' as a column, and then use that as a subquery to the PIVOT command.

If you'd like better tested code, check out the first link in my signature to show you how to set it up so we can easily use it.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

VENUGOPAL.RR Ten Centuries Points: 1395 More actions · Answer 2

pivot will work only if each rid has given number of pids.

In your case it is 3.

if any rid having more then 3 pids it will fails.

Guvera SSC-Addicted Points: 426 More actions · Answer 3

Hi,

Thanks for your reply. I am using PIVOT table concept already. I am using Oracle 10g. 10g version is not supported PIVOT Table.

Thats why i think that using sql query through oracle 10g is it possible?

Thanks

Guvera SSC-Addicted Points: 426 More actions · Answer 4

Hi,