473,320 Members | 1,856 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 473,320 developers and data experts.

Pivoting - 2

debasisdas
8,127 Expert 4TB
Displaying Histogram-Horizontal
===========================
Expand|Select|Wrap|Line Numbers
  1. select deptno,lpad('*',count(*),'*') as cnt from emp group by deptno
Histogram-Vertical
===============
Expand|Select|Wrap|Line Numbers
  1. select row_number( )over(partition by deptno order by empno) rn,
  2. case when deptno=10 then '*' else null end deptno_10,
  3. case when deptno=20 then '*' else null end deptno_20,
  4. case when deptno=30 then '*' else null end deptno_30
  5. from emp
Displaying only the histogram
============================
Expand|Select|Wrap|Line Numbers
  1. select max(deptno_10) d10,
  2.        max(deptno_20) d20,
  3.        max(deptno_30) d30
  4.   from (
  5. select row_number( )over(partition by deptno order by empno) rn,
  6.        case when deptno=10 then '*' else null end deptno_10,
  7.        case when deptno=20 then '*' else null end deptno_20,
  8.        case when deptno=30 then '*' else null end deptno_30
  9.   from emp
  10.        ) x
  11.  group by rn
  12.  order by 1 desc, 2 desc, 3 desc
Returning Non-GROUP BY Columns
============================
Expand|Select|Wrap|Line Numbers
  1. select deptno,ename,job,sal,
  2.        case when sal = max_by_dept
  3.             then 'TOP SAL IN DEPT'
  4.             when sal = min_by_dept
  5.             then 'LOW SAL IN DEPT'
  6.        end dept_status,
  7.        case when sal = max_by_job
  8.             then 'TOP SAL IN JOB'
  9.             when sal = min_by_job
  10.             then 'LOW SAL IN JOB'
  11.        end job_status
  12.   from (
  13. select deptno,ename,job,sal,
  14.        max(sal)over(partition by deptno) max_by_dept,
  15.        max(sal)over(partition by job)   max_by_job,
  16.        min(sal)over(partition by deptno) min_by_dept,
  17.        min(sal)over(partition by job)   min_by_job
  18.   from emp
  19.        ) emp_sals
  20.  where sal in (max_by_dept,max_by_job,
  21.                min_by_dept,min_by_job)
Using ROLLUP to display total
=============================
Expand|Select|Wrap|Line Numbers
  1. select case grouping(job)
  2.             when 0 then job
  3.             else 'TOTAL'
  4.        end job,
  5.        sum(sal) sal
  6.   from emp
  7.  group by rollup(job)
ROLLUP (10g):- TO GENERATE TOTALS AND SUB-TOTALS IN THE SUMMERISED RESULT.IT CAN ONLY APPEAR IN A QUERY WITH A GROUP BY CLAUSE.

Expand|Select|Wrap|Line Numbers
  1. select empno,sum(sal) from emp group by rollup(empno);
  2.  
  3. select empno,ename,sum(sal) from emp group by rollup(empno,ename);
  4.  
  5. select empno,ename,sum(sal) from emp group by rollup(ename,empno);
  6.  
  7. select empno,ename,sum(sal),avg(sal) from emp group by rollup(ename,empno);
  8.  
  9. select grouping(empno),empno,ename,sum(sal),avg(sal) from emp group by rollup(ename,empno);


Also check Pivoting - 3
Sep 17 '07 #1
0 3796

Sign in to post your reply or Sign up for a free account.

Similar topics

226
by: Stephen C. Waterbury | last post by:
This seems like it ought to work, according to the description of reduce(), but it doesn't. Is this a bug, or am I missing something? Python 2.3.2 (#1, Oct 20 2003, 01:04:35) on linux2 Type...
2
by: bher2 | last post by:
gud day. please help me. im working right now on a case study that will retrieve/produce a simple report on sql. my problem is I dont know how to pivot queries like in access. please help me....
3
by: Gert v O | last post by:
Can someone help me parsing this ms-access PIVOT sql-statement to a ms-sql-server sql-statement? Many thanks in advance TRANSFORM Count(KlantenStops.id) AS AantalVanid SELECT...
7
by: FiveFootUnder | last post by:
Hi. Ow. I have a bruised forehead from banging my head against a brick wall and would really appreciate some help here. As long as it's not a suggestion that I shouldn't be trying this!! I'm...
0
debasisdas
by: debasisdas | last post by:
This article contains some of the tips for PIVOTING the recordset (output of the query) . PIVOTING is mainly used for reporting purpose. Displaying the total number of employees department wise....
0
debasisdas
by: debasisdas | last post by:
Displaying Histogram-Horizontal =========================== select deptno,lpad('*',count(*),'*') as cnt from emp group by deptno Histogram-Vertical =============== select row_number(...
0
debasisdas
by: debasisdas | last post by:
CUBE:-IT GENERATES SUBTOTAL FOR ALL POSSIBLE COMBINATION OF GROUPED COLUMNS. GROUPING SETS:-GENERATES SUMMARY INFORMATION AT THE CHOOSEN LEVEL,WITHOUT INCLUDING ALL THE ROWS PRODUCED BY REGULAR...
0
by: Orbie | last post by:
Hi all, I have the following SQL Server query which retrieves rows i'm interested in: Select Temp_Product_ID, Dept_Name, Section_Name, Commodity_Name, Product_Description, Rank, Guide_Price,...
1
by: MrMob | last post by:
Plz teach me to do that...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.