if OBJECT_ID('ta') IS NOT NULL drop table taCREATE TABLE [dbo].[ta](
[dat] [nvarchar](50) NULL,
[Dep] [nvarchar](50) NULL,
[ProOK] [float] NULL, --成品重
[ProErr] [float] NULL, --废品重
[Eleqnt] [float] NULL --用电量
) ON [PRIMARY]
insert into ta
SELECT '1','ZA',3.92,0.143,3841 UNION ALL
SELECT '1','ZB',4.27,0.143,3823 UNION ALL
SELECT '1','ZC',3.729,0.143,3733 UNION ALL
SELECT '2','ZA',4.05,0.143,3733 UNION ALL
SELECT '2','ZB',4.15,0.143,3735 UNION ALL
SELECT '2','ZC',4.1295,0.143,3725 UNION ALL
SELECT '3','ZA',3.74,0.148,3693 UNION ALL
SELECT '3','ZB',4.23,0.148,3815 UNION ALL
SELECT '3','ZC',3.8939,0.148,3843--想要的结果
/*
DEP 1 2 3
-------------------------
成品重 ZA 3.92
废品重 ZA 0.143
利用率 ZA 96.5%
用电量 ZA 3841
成品重 ZB 4.27
废品重 ZB 0.143
利用率 ZB 96.8%
用电量 ZB 3823
成品重 ZC 3.729
废品重 ZC 0.143
利用率 ZC 96.3%
用电量 ZC 3733
成品合计 11.919
用电合计 11397
1,2,3是指日期...数据与1号的数据一样...
*/
解决方案 »
- 请教一个问题,现在有三个线程同时对一个表做删除和插入操作,数据库是sql server2005
- 语法问题
- 如何查看备份的数据事务日志TRN文件
- 关于使用jdbc在SQLSERVER中创建临时表的问题
- 用户定义函数
- 插入 更新数据库就出现the database is read-only
- select DateValue(RegTime) from xxx...???
- 本地不安装SQL SERVER,能在本地存储管理数据吗?高分求救,解决后马上结分.
- 如何建库?
- 如何取得从sp_executesql返回的结果集
- 写一个存储过程,请大家帮帮忙~
- 高分求解在线等!sqlserver和oracle对接问题!
标题:90度旋转行列转换之一
作者:爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间:2010-05-08
地点:重庆航天职业学院
说明:无
*/
/*
数据库中tb表格如下
月份 工资 福利 奖金
1月 100 200 300
2月 110 210 310
3月 120 220 320
4月 130 230 330我想得到的结果是项目 1月 2月 3月 4月
工资 100 110 120 130
福利 200 210 220 230
奖金 300 310 320 330就是说完全把表格的行列颠倒,有点像那种旋转矩阵,请问如何用sql 语句实现?
*/
/*--行列互换的通用存储过程(原著:邹建):将指定的表,按指定的字段进行行列互换*/
create proc p_zj
@tbname sysname, --要处理的表名
@fdname sysname, --做为转换的列名
@new_fdname sysname='' --为转换后的列指定列名
as
declare @s1 varchar(8000) , @s2 varchar(8000),
@s3 varchar(8000) , @s4 varchar(8000),
@s5 varchar(8000) , @i varchar(10)
select @s1 = '' , @s2 = '' , @s3 = '' , @s4 = '' , @s5 = '' , @i = '0'
select @s1 = @s1 + ',@' + @i + ' varchar(8000)',
@s2 = @s2 + ',@' + @i + '=''' + case isnull(@new_fdname , '') when '' then ''
else @new_fdname + '=' end + '''''' + name + '''''''',
@s3 = @s3 + 'select @' + @i + '=@' + @i + '+'',['' + [' + @fdname +
']+'']=''+cast([' + name + '] as varchar) from [' + @tbname + ']',
@s4 = @s4 + ',@' + @i + '=''select ''+@' + @i,
@s5 = @s5 + '+'' union all ''+@' + @i,
@i=cast(@i as int)+1
from syscolumns
where object_id(@tbname)=id and name<>@fdnameselect @s1=substring(@s1,2,8000),
@s2=substring(@s2,2,8000),
@s4=substring(@s4,2,8000),
@s5=substring(@s5,16,8000)
exec('declare ' + @s1 + 'select ' + @s2 + @s3 + 'select ' + @s4 + '
exec(' + @s5 + ')')
go--创建测试数据
create table Test(月份 varchar(4), 工资 int, 福利 int, 奖金 int)
insert Test
select '1月',100,200,300 union all
select '2月',110,210,310 union all
select '3月',120,220,320 union all
select '4月',130,230,330
go--用上面的存储过程测试:
exec p_zj 'Test', '月份' , '项目'drop table Test
drop proc p_zj/*
项目 1月 2月 3月 4月
-------- ------ -------- -------- --------
奖金 300 310 320 330
工资 100 110 120 130
福利 200 210 220 230(所影响的行数为 3 行)
*/--SQL2005静态写法
--创建测试数据
create table Test(月份 varchar(4), 工资 int, 福利 int, 奖金 int)
insert Test
select '1月',100,200,300 union all
select '2月',110,210,310 union all
select '3月',120,220,320 union all
select '4月',130,230,330
goSELECT * FROM
(
SELECT 考核月份,月份,金额 FROM
(SELECT 月份, 工资, 福利, 奖金 FROM Test) p
UNPIVOT
(金额 FOR 考核月份 IN (工资, 福利, 奖金))AS unpvt
) T
PIVOT
(MAX(金额) FOR 月份 in ([1月],[2月],[3月],[4月]))AS ptdrop table test/*
项目 1月 2月 3月 4月
-------- ------ -------- -------- --------
奖金 300 310 320 330
工资 100 110 120 130
福利 200 210 220 230(3 行受影响)
*/
select @s=isnull(@s+' union all ','')+'select [dep],[名称]='+quotename(name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第一个union all
+',[ProOK]='+quotename(name)+' from ta'
from syscolumns where ID=object_id('ta') and Name not in('dep')--排除不转换的列
order by Colid
print('select * from ('+@s+')t order by [dep],[prook]')--增加一个排序
(
select dep, dat, '成品重'as t,p=CAST([ProOk]AS varchar) from ta union all
select dep, dat, '废品重'as t,p=CAST([ProErr]AS varchar) from ta union all
select dep, dat, '利用率'as t,P=CAST(round(([ProOK]-[ProErr])/[ProOk],4)* 100 AS varchar) + '%' from ta union all
select dep, dat, '用电量'as t,p=CAST([Eleqnt]AS varchar) from ta union all
SELECT '合计' AS dep, dat, '成品' AS t, CAST(SUM(ProOK)AS varchar) AS p FROM ta GROUP BY dat union all
SELECT '合计' AS dep, dat, '用电量' AS t, CAST(SUM(Eleqnt)AS varchar) AS p FROM ta GROUP BY dat
) T
PIVOT
(MAX(p) FOR dat in ([1],[2],[3]))AS pt
ORDER BY dep
if OBJECT_ID('ta') IS NOT NULL drop table taCREATE TABLE [dbo].[ta](
[dat] [nvarchar](50) NULL,
[Dep] [nvarchar](50) NULL,
[ProOK] [float] NULL, --成品重
[ProErr] [float] NULL, --废品重
[Eleqnt] [float] NULL --用电量
) ON [PRIMARY]
insert into ta
SELECT '1','ZA',3.92,0.143,3841 UNION ALL
SELECT '1','ZB',4.27,0.143,3823 UNION ALL
SELECT '1','ZC',3.729,0.143,3733 UNION ALL
SELECT '2','ZA',4.05,0.143,3733 UNION ALL
SELECT '2','ZB',4.15,0.143,3735 UNION ALL
SELECT '2','ZC',4.1295,0.143,3725 UNION ALL
SELECT '3','ZA',3.74,0.148,3693 UNION ALL
SELECT '3','ZB',4.23,0.148,3815 UNION ALL
SELECT '3','ZC',3.8939,0.148,3843SELECT * FROM
(
select dep, dat, '成品重'as t,p=CAST([ProOk]AS varchar) from ta union all
select dep, dat, '废品重'as t,p=CAST([ProErr]AS varchar) from ta union all
select dep, dat, '利用率'as t,P=CAST(round([ProOK]/([ProErr]+[ProOk]),4)* 100 AS varchar) + '%' from ta union all
select dep, dat, '用电量'as t,p=CAST([Eleqnt]AS varchar) from ta union all
SELECT '合计' AS dep, dat, '成品' AS t, CAST(SUM(ProOK)AS varchar) AS p FROM ta GROUP BY dat union all
SELECT '合计' AS dep, dat, '用电量' AS t, CAST(SUM(Eleqnt)AS varchar) AS p FROM ta GROUP BY dat
) T
PIVOT
(MAX(p) FOR dat in ([1],[2],[3]))AS pt
ORDER BY dep(9 行受影响)
dep t 1 2 3
-------------------------------------------------- ------ ------------------------------- ------------------------------- -------------------------------
ZA 成品重 3.92 4.05 3.74
ZA 废品重 0.143 0.143 0.148
ZA 利用率 96.48% 96.59% 96.19%
ZA 用电量 3841 3733 3693
ZB 成品重 4.27 4.15 4.23
ZB 废品重 0.143 0.143 0.148
ZB 利用率 96.76% 96.67% 96.62%
ZB 用电量 3823 3735 3815
ZC 成品重 3.729 4.1295 3.8939
ZC 废品重 0.143 0.143 0.148
ZC 利用率 96.31% 96.65% 96.34%
ZC 用电量 3733 3725 3843
合计 成品 11.919 12.3295 11.8639
合计 用电量 11397 11193 11351(14 行受影响)