sql PIVOT,UNPIVOT,GROUPING SETS

最新推荐文章于 2024-01-18 14:21:41 发布

原创最新推荐文章于 2024-01-18 14:21:41 发布 · 1.2k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #pivot #null #table #insert #object

MSSQL 专栏收录该内容

64 篇文章

订阅专栏

本文介绍如何使用SQL进行订单数据的操作与分析，包括创建订单表、插入数据、分组查询以及利用聚合函数进行数据汇总，同时展示了SQL的动态查询、PIVOT与UNPIVOT方法，并探讨了数据分组与立方体聚合的概念。

if OBJECT_ID('Orders')is not null
drop table Orders
go
create table Orders
(
orderid int not null,
orderdate date not null,
empid int not null,
cusid varchar(5)not null,
qty int not null,
constraint PK_Order primary key(orderid)
)
insert into Orders
(
orderid,orderdate,empid,cusid,qty
)
select 30001,'20070802',3,'A',10
union all
select 10001,'20071224',2,'A',12
union all
select 10005,'20071224',1,'B',20
union all 
select 40001,'20080109',2,'A',40
union all 
select 10006,'20080118',1,'C',14
union all
select 20001,'20080212',2,'B',12
union all 
select 40005,'20080212',3,'A',10
union all 
select 20002,'20080216',1,'C',20
union all
select 20003,'20090418',2,'B',15
union all
select 30004,'20070418',3,'C',
22
union all
select 30007,'20090907',3,'D',30
go
--sql2000Staticsql
select empid,
		SUM(case when cusid='A'then qty else 0 end)'A',
		SUM(case when cusid='B'then qty else 0 end)'B',
		SUM(case when cusid='C'then qty else 0 end)'C',
		SUM(case when cusid='D'then qty else 0 end)'D'
from
Orders group by empid
--sql2000Dynamicsql

declare @sql varchar(2000)
set @sql='select empid'
select @sql=@sql+',sum(case when cusid='''+O.cusid+''' then qty else 0 end)['+O.cusid+']'
from(select distinct cusid from Orders)O
set @sql=@sql+'from Orders group by empid'
exec(@sql)
--PIVOTMethod
select empid,A,B,C,D
--intoCusOrders
from
(
select empid,cusid,qty from Orders
)
sourceOrder
pivot
(
sum(qty)
for cusid in(A,B,C,D)
)
as
targetOrder
--UNPIVOTMethod
select
empid,cusid,qty from CusOrders
unpivot
(
qty for cusid in(A,B,C,D)
)
as[Target]
--Groupingsets
select empid,cusid,SUM(qty) qty
from Orders
group by Grouping
sets
(
(empid,cusid),
(empid),
(cusid),
()
)
--cube

--CubeisequivalenttoGROUPINGSets

select empid,cusid,SUM(qty) qty 
from Orders
group by cube(empid,cusid)