Home » SQL & PL/SQL » SQL & PL/SQL » How to Use Cross Tab query
How to Use Cross Tab query [message #187879] Wed, 16 August 2006 03:21 Go to next message
annappa
Messages: 5
Registered: August 2006
Location: Bangalore
Junior Member
Hi All,

I have query in MS-Access but i need to covert it in Oracle Query. I have tried lot but i didn't get proper solution:

MS-Access Query..


TRANSFORM First(IIf(tab1.ID Is Not Null, X)) AS Check
SELECT
tab2.ID,
tab2.NAME,
tab2.DEP,
Sum(IIf(Check=X, 1)) AS TotalWhereUsed
FROM
tab2 LEFT JOIN tab1 ON tab2.ID = tab1.ID

GROUP BY
tab2.ID,
tab2.NAME,
tab2.DEP,

ORDER BY
tab2.ID,
tab1.LOC
PIVOT
tab1.LOC


Mainly am facing "tab1.loc" it contains more then 50 values. How we can make it columns.

Pls help me.

Regards,
DRA
Re: How to Use Cross Tab query [message #187893 is a reply to message #187879] Wed, 16 August 2006 04:43 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Hi annappa
This is quite a commonly asked question here. Search the forums for the keyword PIVOT. Youwill find lots and lots of examples.

Jim
Re: How to Use Cross Tab query [message #187913 is a reply to message #187893] Wed, 16 August 2006 06:15 Go to previous messageGo to next message
annappa
Messages: 5
Registered: August 2006
Location: Bangalore
Junior Member
Hi,

Searched lot..all are limited columns..

But i have more then 50 rows, need to convert this column to rows heading...

If have seen this type in forum..pls past that URL...i tryed lot...

Thanks in Advance...

DRA
Re: How to Use Cross Tab query [message #187921 is a reply to message #187913] Wed, 16 August 2006 06:26 Go to previous message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Is it a fixed number of rows to convert to columns? Take the hit use the pivot answers that you have already found. If the number of columns is dynamic, then you need to use PL/SQL
Here is 1
http://www.orafaq.com/forum/m/125034/0/?srch=pivot#msg_125034
here is another
http://www.orafaq.com/forum/m/30557/68488/?srch=pivot+dynamic#msg_30557

Jim
Previous Topic: Urgent helpr with PL/SQL
Next Topic: Date Format
Goto Forum:
  


Current Time: Tue Dec 06 16:11:17 CST 2016

Total time taken to generate the page: 0.12494 seconds