www.3112.net > sql 转置函数

sql 转置函数

1. SQL 表转置关键字 case when ,pivot2. 在 SQL Server 2005 之前,通常需要通过 case 子句来处理.3. SQL 2005 后面可以使用pivot4. 下面介绍case when 用法,SQL 如下:5. select ProductID, sum( case when OrderMonth = 5 then SubTotal

select ITEM,sum((case when column1='A' then qty1 else 0 end)) Aqty,sum((case when column1='B' then qty1 else 0 end)) Bqty from table group ITEM

declare @sql varchar(2000)set @sql=''select @sql=@sql+',max(case B when '+cast(B as varchar(10))+' then c end) ['+cast(B as varchar(10))+']' from (Select distinct B from TB) xset @sql='select A'+@sql+' from (select A,B,count(distinct C) c from tbgroup by A,B) vgroup by a'exec(@sql)

select ID,Name,Typesum(case riqi when '20110101' then count end) as '20110101',sum(case riqi when '20110102' then count end) as '20110102',sum(case riqi when '20110103' then count end) as '20110103'from table_namegroup by ID,Name,Type

create table a(id int, val varchar(10)) go insert into a values(10001,'45.4') insert into a values(10001,'51.81') insert into a values(10001,'20.52') insert into a values(10001,'64.11') insert into a values(10001,'5.036') go create function f1(@id int)

有专门的行专列内置函数,oracle的叫pivot,你可以研究下.可以实现你要的效果

select col1,col2,sum(decode(col3,'X',col4)) X,sum(decode(col3,'Y',col4)) Y,sum(decode(col3,'Z',col4)) Zfrom tablenamegroup by col1,col2

declare @sql varchar(8000)declare @date varchar(20)declare @bmbh varchar(20)declare @ckbh varchar(20)set @date='20110101'set @bmbh='500103'set @ckbh='0601' set @sql = 'select max(lsbmzd_bmmc) as bmmc,kcrkd2_wlbh as wlbh,max(

select 厂家,sum(case when 列别 = 棉衣 then 1 else 0 end) 棉衣,sum(case when 列别 = 风衣 then 1 else 0 end) 风衣 ,sum(case when 列别 = 背心 then 1 else 0 end) 背心from 表group by 厂家

问题:假设有张学生成绩表(tb)如下:姓名 课程 分数张三 语文 74张三 数学 83张三 物理 93李四 语文 74李四 数学 84李四 物理 94想变成(得到如下结果): 姓名 语文 数学 物理 ---- ---- ---- ----李四 748494张三 748393-------------------*/ create

网站地图

All rights reserved Powered by www.3112.net

copyright ©right 2010-2021。
www.3112.net内容来自网络,如有侵犯请联系客服。zhit325@qq.com