Toad World® Forums

Crosstab query


#1

Is it possible to create crosstab queries in Toad?

and if so how?

please advise thank you

juman


#2

Yes. You can make a cross tab query by using calculated fields in the Query Builder. But it is much easier to use a pivot grid to do the aggregations. To use the pivot grid, right click on any result set (after building a query with the un-aggregated rows) and choose ‘Send To || Pivot Grid’.

If you need more detail on either method, let me know more detail on what you want to accomplish and what database type you are using.

Debbie


#3

Hey Debbie,

I appreciate your response thank you

How can i use pivot grid please see example of what i am trying to acheive

STATE
ADMIN
HIT
HME
HOSPICE
MED
O/P
RESP
THH
WHS
OK

X

X

GA

X

X
X

IL

X

TX

X

in access field First(“X”) expression if servicecatid <>“EBA” And <>“DISP” And <>“REHAB” And <>“INSTAY” And <>“LAB” And <>“SUBACUTE” and so forth

i hope this will give you a good idea of what i am trying to do
Juman


#4

if to use calculated fields what function i should use to tell it if servicecategory is HME then put X in this cell under HME column and if DISP put X under column titled DISP and so forth

please advise thank you
Juman


#5

If you are using Oracle, use a Case statement, or equivalent on other platforms.

Here is sample.

SELECT EMPLOYEES.FIRST_NAME,
EMPLOYEES.LAST_NAME,
JOBS.MIN_SALARY,
CASE
WHEN MIN_SALARY
WHEN MIN_SALARY BETWEEN 10000 AND 25000 THEN ‘Ok’
WHEN MIN_SALARY > 25000 THEN ‘MIDDle Ground’
ELSE ‘Please review their salary’
END
AS “Comment”
FROM HR.EMPLOYEES EMPLOYEES
INNER JOIN
HR.JOBS JOBS
ON (EMPLOYEES.JOB_ID = JOBS.JOB_ID)


#6

T1.PROVID,

T1.FEDERALTAXID,

T1.SHORTNAME,

T1.PROVNAME,

AS ADMIN,

SUM (CASE WHEN T2.SERVICECATID = ‘HIT’ THEN ‘1’ ELSE NULL END) AS HIT…

actually what i did as follows (see some of the code), but i need output to be a character ‘X’ and not numbers, any suggestions? thank you juman

SELECT T1.PROVPARENTID AS PARID,SUM (CASE WHEN T2.SERVICECATID = ‘ADMIN’ THEN ‘1’ ELSE NULL END)SUM(CASE WHEN T2.SERVICECATID = ‘HME’ THEN ‘1’ ELSE NULL END) AS HME,


#7

We made a similiar query using the Oracle scott schema. When you use a query like the one below I see no way to display an ‘X’ value from a numerical result.

SELECT DEPARTMENT.LOC, SUM (CASE WHEN DEPARTMENT.DNAME = ‘ACCOUNTING’ THEN 1 ELSE 0 END) AS ACOUNTING, SUM (CASE WHEN DEPARTMENT.DNAME = ‘RESEARCH’ THEN 1 ELSE 0 END) AS RESEARCH, SUM (CASE WHEN DEPARTMENT.DNAME = ‘SALES’ THEN 1 ELSE 0 END) AS SALES FROM SCOTT.EMPLOYEE EMPLOYEE INNER JOIN SCOTT.DEPARTMENT DEPARTMENT ON (EMPLOYEE.DEPTNO = DEPARTMENT.DEPTNO)GROUP BY DEPARTMENT.LOCORDER BY DEPARTMENT.LOC ASC

Instead if you use a query that does not do the aggregation and instead use a pivot grid, you can change the filter to display the ‘X’ like you want.

SELECT DEPARTMENT.DNAME, EMPLOYEE.EMPNO, DEPARTMENT.LOC FROM SCOTT.EMPLOYEE EMPLOYEE INNER JOIN SCOTT.DEPARTMENT DEPARTMENT ON (EMPLOYEE.DEPTNO = DEPARTMENT.DEPTNO)

Using this SQL, see the second screenshot. The Pivot grid is a new tab in the editor/Query Builder in version 2.5. Or can be accessed from right click from the result tab. Once in the Pivot Grid, pivot the data and then right click and use the ‘Setup Grid’ action to get to the format editor.

I think this will give you what you need.

Debbie
CrossTab2.png


#8

We made a similiar query using the Oracle scott schema. When you use a query like the one below I see no way to display an ‘X’ value from a numerical result.

SELECT DEPARTMENT.LOC, SUM (CASE WHEN DEPARTMENT.DNAME = ‘ACCOUNTING’ THEN 1 ELSE 0 END) AS ACOUNTING, SUM (CASE WHEN DEPARTMENT.DNAME = ‘RESEARCH’ THEN 1 ELSE 0 END) AS RESEARCH, SUM (CASE WHEN DEPARTMENT.DNAME = ‘SALES’ THEN 1 ELSE 0 END) AS SALES FROM SCOTT.EMPLOYEE EMPLOYEE INNER JOIN SCOTT.DEPARTMENT DEPARTMENT ON (EMPLOYEE.DEPTNO = DEPARTMENT.DEPTNO)GROUP BY DEPARTMENT.LOCORDER BY DEPARTMENT.LOC ASC

Instead if you use a query that does not do the aggregation and instead use a pivot grid, you can change the filter to display the ‘X’ like you want.

SELECT DEPARTMENT.DNAME, EMPLOYEE.EMPNO, DEPARTMENT.LOC FROM SCOTT.EMPLOYEE EMPLOYEE INNER JOIN SCOTT.DEPARTMENT DEPARTMENT ON (EMPLOYEE.DEPTNO = DEPARTMENT.DEPTNO)

Using this SQL, see the second screenshot. The Pivot grid is a new tab in the editor/Query Builder in version 2.5. Or can be accessed from right click from the result tab. Once in the Pivot Grid, pivot the data and then right click and use the ‘Setup Grid’ action to get to the format editor.

I think this will give you what you need.

Debbie
CrossTab1.png


#9

thank you i will give it a try :slight_smile:
juman


#10

hey Debbie,

I followed your instruction but for some reason i am not getting value of X and please see attched first file it shows the $ sign and second file is the final outcome as suppose to be blank and x

i appreciate your help thank you
juman
untitled2.jpg


#11

hey Debbie,

I followed your instruction but for some reason i am not getting value of X and please see attched first file it shows the $ sign and second file is the final outcome as suppose to be blank and x

i appreciate your help thank you
juman
untitled1.jpg


#12

Right click on the Pivot Grid and choose ‘SetUp Grid’. Choose the column you want this format to be applied. Select the ‘Cell Format’ and invoke the editor for formatting. Select the Number type and Custom tab. Enter X and click OK.
See attached screenshot.
X.png


#13

hello Debbie,

i saw that you mentioned here a ‘Pivot Grid’. In which tool i can find it?

I’m using toad for Oracle 12.1 with DBA option.

Thanks

Martin


#14

It is in Toad Data Point. It is a tab on the result set.

Pivotchart.png