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.
============================================= - select deptno,count(deptno) from emp group by deptno;
Pivoting the same output.
===================== - select sum(case when deptno=10 then 1 else 0 end) as deptno_10,
-
sum(case when deptno=20 then 1 else 0 end) as deptno_20,
-
sum(case when deptno=30 then 1 else 0 end) as deptno_30,
-
sum(case when deptno=40 then 1 else 0 end) as deptno_40
-
from emp;
Selecting employees name group by there job.
====================================== -
select max(case when job='CLERK'
-
then ename else null end) as clerks,
-
max(case when job='ANALYST'
-
then ename else null end) as analysts,
-
max(case when job='MANAGER'
-
then ename else null end) as mgrs,
-
max(case when job='PRESIDENT'
-
then ename else null end) as presi,
-
max(case when job='SALESMAN'
-
then ename else null end) as sales
-
from (
-
select job,
-
ename,
-
row_number()over(partition by job order by ename) rn
-
from emp
-
) x
-
group by rn
Reverse Pivoting
============= - select dept.deptno,
-
case dept.deptno
-
when 10 then emp_cnts.deptno_10
-
when 20 then emp_cnts.deptno_20
-
when 30 then emp_cnts.deptno_30
-
end as counts_by_dept
-
from (
-
Select sum(case when deptno=10 then 1 else 0 end) as deptno_10,
-
sum(case when deptno=20 then 1 else 0 end) as deptno_20,
-
sum(case when deptno=30 then 1 else 0 end) as deptno_30
-
from emp
-
) emp_cnts,
-
(select deptno from dept where deptno <= 30) dept
Display in single column
===================== - select case rn
-
when 1 then ename
-
when 2 then job
-
when 3 then cast(sal as char(4))
-
end emps
-
from (
-
select e.ename,e.job,e.sal,
-
row_number()over(partition by e.empno
-
order by e.empno) rn
-
from emp e,
-
(select *
-
from emp where job='CLERK') four_rows
-
where e.deptno=10
-
) x
Suppressing Repeating Values from a Result Set
======================================== - select to_number(
-
decode(lag(deptno)over(order by deptno),
-
deptno,null,deptno)
-
) deptno, ename
-
from emp
Findout the Difference of sum of sal among groups department wise
================================================== == - select d20_sal - d10_sal as d20_10_diff,
-
d20_sal - d30_sal as d20_30_diff
-
from (
-
select sum(case when deptno=10 then sal end) as d10_sal,
-
sum(case when deptno=20 then sal end) as d20_sal,
-
sum(case when deptno=30 then sal end) as d30_sal
-
from emp
-
) totals_by_dept
Also Check Povoting - 2 0 4826 Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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....
|
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...
|
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...
|
by: debasisdas |
last post by:
Displaying Histogram-Horizontal
===========================
select deptno,lpad('*',count(*),'*') as cnt from emp group by deptno
Histogram-Vertical
===============
select row_number(...
|
by: debasisdas |
last post by:
Displaying Histogram-Horizontal
===========================
select deptno,lpad('*',count(*),'*') as cnt from emp group by deptno
Histogram-Vertical
===============
select row_number(...
|
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...
|
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,...
|
by: MrMob |
last post by:
Plz teach me to do that...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |