22,210
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE tb1
(
姓名 varchar(10) ,
语文 int ,
数学 int ,
物理 int
)
INSERT INTO tb1(姓名 , 语文 , 数学 , 物理) values('张三',74,83,93)
insert into tb1(姓名 , 语文 , 数学 , 物理) values('李四',74,84,94)
select*from
(
select 姓名 as Name , Subject ='语文' , Result = 语文 from tb1
UNION ALL
select 姓名 as Name , Subject ='数学' , Result = 数学 from tb1
UNION ALL
select 姓名 as Name , Subject ='物理' , Result = 物理 from tb1
) t
ORDER BY name , case Subject when'语文'THEN 1 when'数学'THEN 2 WHEN'物理'THEN 3 WHEN'总分'THEN 4 END
--测试数据
if not object_id('tab') is null
drop table tab
Go
Create table tab(a0 NVARCHAR(100),a1 int,a2 int,a3 int)
Insert tab
select '2017/1/18',1,11,111 UNION all
select '2017/1/19',2,22,222 union all
select '2017/1/20',3,33,333
go
--测试数据结束
DECLARE @s NVARCHAR(4000) ,
@s2 NVARCHAR(4000) ,
@s3 NVARCHAR(4000) ,
@s4 NVARCHAR(4000)
SELECT @s = ISNULL(@s + ',', 'declare ') + '@' + RTRIM(Colid)
+ ' nvarchar(4000)' ,
@s2 = ISNULL(@s2 + ',', 'select ') + '@' + RTRIM(Colid) + '='''
+ CASE WHEN @s2 IS NOT NULL THEN 'union all select'
ELSE ' select '
END + ' a0=''' + QUOTENAME(Name, '''') + '''''' ,
@s3 = ISNULL(@s3, '') + 'select @' + RTRIM(Colid) + '=@' + RTRIM(Colid)
+ '+'',''+quotename([a0])+''=''+quotename(' + QUOTENAME(Name)
+ ','''''''') from tab ' ,
@s4 = ISNULL(@s4 + '+', '') + '@' + RTRIM(Colid)
FROM syscolumns
WHERE id = OBJECT_ID('tab')
AND Name NOT IN ( 'a0' )
EXEC(@s+' '+@s2+' '+@s3+' exec('+@s4+')')
/*
数据库中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 语句实现?
*/
if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[p_zj]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_zj]
GO
/*--行列互换的通用存储过程(原著:邹建):将指定的表,按指定的字段进行行列互换*/
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<>@fdname
select @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月
---- ----------- ----------- ----------- -----------
福利 200 210 220 230
工资 100 110 120 130
奖金 300 310 320 330
(所影响的行数为 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
go
--测试语句
SELECT * FROM
(
SELECT 考核月份,月份,金额 FROM
(SELECT 月份, 工资, 福利, 奖金 FROM Test) p
UNPIVOT
(金额 FOR 考核月份 IN (工资, 福利, 奖金))AS unpvt
) T
PIVOT
(MAX(金额) FOR 月份 in ([1月],[2月],[3月],[4月]))AS pt
--测试结果
/*
考核月份 1月 2月 3月 4月
------- ----- ----- ------ -------
福利200210220230
工资100110120130
奖金300310320330
*/
--删除环境
Drop table Test
懒得写了,给个你自己看