首页 技术 正文
技术 2022年11月23日
0 收藏 889 点赞 4,976 浏览 3375 个字
--建表
create table dbo.orders
( orderid int not null primary key nonclustered,
orderdate datetime not null,
empid int not null,
custid varchar(5) not null,
qty int not null)
--插入数据
insert into orders values(30001,'',3,'a',10)
insert into orders values(10001,'',1,'a',12)
insert into orders values(10005,'',1,'b',20)
insert into orders values(40001,'',4,'a',40)
insert into orders values(10006,'',1,'c',14)
insert into orders values(20001,'',2,'b',12)
insert into orders values(40005,'',4,'a',10)
insert into orders values(20002,'',2,'c',20)
insert into orders values(30003,'',3,'b',15)
insert into orders values(30004,'',3,'c',22)
insert into orders values(30007,'',3,'d',30)--数据。
select custid,year(orderdate) as orderdate ,qty from orders order by custidcustid orderdate qty
------ ----------- -----------
a 2002 10
a 2002 12
a 2003 40
a 2004 10
b 2004 15
b 2003 12
b 2002 20
c 2002 22
c 2003 14
c 2004 20
d 2002 30
(11 行受影响)--转阿转。
select custid,sum(case when orderdate=2002 then qty end) as [],
sum(case when orderdate=2003 then qty end) as [],
sum(case when orderdate=2004 then qty end) as []
from ( select custid,year(orderdate) as orderdate ,qty from orders ) as d group by custid;custid 2002 2003 2004
------ ----------- ----------- -----------
a 22 40 10
b 20 12 15
c 22 14 20
d 30 NULL NULL
警告: 聚合或其他 SET 操作消除了空值。
(4 行受影响)--行转列
select * from (select custid,year(orderdate) as orderyear,qty from dbo.orders) as d
pivot (sum(qty) for orderyear in ([],[],[])) as p;custid 2002 2003 2004
------ ----------- ----------- -----------
a 22 40 10
b 20 12 15
c 22 14 20
d 30 NULL NULL
(4 行受影响)--反转。
select custid,orderyear,qty from pvtt unpivot ( qty for orderyear in ([],[],[])) as u; custid orderyear qty
------ -------------------------------------------------------------------------------------------------------------------------------- -----------
a 2002 22
a 2003 40
a 2004 10
b 2002 20
b 2003 12
b 2004 15
c 2002 22
c 2003 14
c 2004 20
d 2002 30
(10 行受影响)--动态反转。注意@b变量要放在括号里,否则报不是有效的标识符
declare @a varchar(4000),@b varchar(4000)
set @a=''
select @a=@a+'],['+cast(a as varchar(10)) from (select distinct year(orderdate) as a from orders) as b
set @a=right(@a,len(@a)-2)+']'
set @b='select custid,orderyear,qty from pvtt unpivot (qty for orderyear in ('+ @a +')) as c'
exec (@b)custid orderyear qty
------ ----------------------------------------------------------------------------------------
a 2002 22
a 2003 40
a 2004 10
b 2002 20
b 2003 12
b 2004 15
c 2002 22
c 2003 14
c 2004 20
d 2002 30
(10 行受影响)--动态反转,注意DISTINCT引用否则则提示多次引用列。
declare @a varchar(4000),@b varchar(4000)
set @a=''
select @a=@a+aa from (select distinct (select '['+cast ( a as varchar(10))+'],' as [text()] from (select distinct year(orderdate) as a from orders
) as a3 for xml path('')) as aa from (select distinct year(orderdate) as a from orders
) as a2) as c
set @a=left(@a,len(@a)-1)
set @b='select custid,orderyear,qty from pvtt unpivot (qty for orderyear in ('+ @a +')) as c'
exec (@b)custid orderyear qty
------ --------------------------------------------------------------------------------------
a 2002 22
a 2003 40
a 2004 10
b 2002 20
b 2003 12
b 2004 15
c 2002 22
c 2003 14
c 2004 20
d 2002 30
(10 行受影响)--通过系统视图(INFORMAT_SCHEMA.COLUMNS)拉列表。
declare @a as table( y int not null primary key );
declare @cols as nvarchar(max)
declare @sql as nvarchar(max)
set @cols=stuff(
(select N','+quotename(y) as [text()] from
(select column_name as y from INFORMATION_SCHEMA.COLUMNS
where table_schema=N'dbo' and table_name=N'pvtt' and column_name not in (N'custid')) as y
order by y for xml path('')),1 ,1 ,N'');
set @sql=N'select custid,orderyear,qty from dbo.pvtt unpivot(qty for orderyear in ('+@cols+N')) as u;';
exec sp_executesql @sqlcustid orderyear qty
------ -------------------------------------------------------------------------------------
a 2002 22
a 2003 40
a 2004 10
b 2002 20
b 2003 12
b 2004 15
c 2002 22
c 2003 14
c 2004 20
d 2002 30
(10 行受影响)
相关推荐
python开发_常用的python模块及安装方法
adodb:我们领导推荐的数据库连接组件bsddb3:BerkeleyDB的连接组件Cheetah-1.0:我比较喜欢这个版本的cheeta…
日期:2022-11-24 点赞:878 阅读:8,933
Educational Codeforces Round 11 C. Hard Process 二分
C. Hard Process题目连接:http://www.codeforces.com/contest/660/problem/CDes…
日期:2022-11-24 点赞:807 阅读:5,454
下载Ubuntn 17.04 内核源代码
zengkefu@server1:/usr/src$ uname -aLinux server1 4.10.0-19-generic #21…
日期:2022-11-24 点赞:569 阅读:6,267
可用Active Desktop Calendar V7.86 注册码序列号
可用Active Desktop Calendar V7.86 注册码序列号Name: www.greendown.cn Code: &nb…
日期:2022-11-24 点赞:733 阅读:6,084
Android调用系统相机、自定义相机、处理大图片
Android调用系统相机和自定义相机实例本博文主要是介绍了android上使用相机进行拍照并显示的两种方式,并且由于涉及到要把拍到的照片显…
日期:2022-11-24 点赞:512 阅读:7,715
Struts的使用
一、Struts2的获取  Struts的官方网站为:http://struts.apache.org/  下载完Struts2的jar包,…
日期:2022-11-24 点赞:671 阅读:4,753