27,579
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据:[tbl]
go
if object_id('[tbl]') is not null
drop table [tbl]
go
create table [tbl](
[m_ID] int,
[m_Div] varchar(1),
[m_DML] int,
[m_Sea] int,
[m_SEN] int
)
go
insert [tbl]
select 322,'A',1,0,20 union all
select 344,'C',56,3,7213 union all
select 341,'A',1,0,22 union all
select 325,'C',23,25,7109 union all
select 353,'B',7,0,39 union all
select 349,'A',6,0,106 union all
select 315,'B',7,0,136
SELECT 'Div' AS ID,*FROM (
SELECT ROW_NUMBER()OVER(order BY GETDATE()) AS ID,[m_ID] FROM tbl
)A pivot (max([m_ID]) for ID in([1],[2],[3],[4],[5],[6],[7]))b
UNION ALL
SELECT 'DML ',*FROM (
SELECT ROW_NUMBER()OVER(order BY GETDATE()) AS ID,[m_DML] FROM tbl
)A pivot (max([m_DML]) for ID in([1],[2],[3],[4],[5],[6],[7]))b
UNION ALL
SELECT 'Sea',*FROM (
SELECT ROW_NUMBER()OVER(order BY GETDATE()) AS ID,[m_Sea] FROM tbl
)A pivot (max([m_Sea]) for ID in([1],[2],[3],[4],[5],[6],[7]))b
UNION ALL
SELECT 'SEN',*FROM (
SELECT ROW_NUMBER()OVER(order BY GETDATE()) AS ID,[m_SEN] FROM tbl
)A pivot (max([m_SEN]) for ID in([1],[2],[3],[4],[5],[6],[7]))b
--用你说的pivot实现
/*
ID 1 2 3 4 5 6 7
Div 322 344 341 325 353 349 315
DML 1 56 1 23 7 6 7
Sea 0 3 0 25 0 0 0
SEN 20 7213 22 7109 39 106 136
*/
create database [test]
go
--drop
--创建表
create table [dbo].[m_tb](
id int identity(1,1) primary key not null ,
[m_id] [varchar](10) null,
[m_div] [varchar](10) null,
[m_dml] [varchar](10) null,
[m_sea] [varchar](10) null,
[m_sen] [varchar](10) null
) on [primary]
go
---插入数据
insert into dbo.m_tb([m_id],m_div,m_dml,m_sea,m_sen) values('322','A','1','0','20')
go
insert into dbo.m_tb([m_id],m_div,m_dml,m_sea,m_sen) values('344','C','56','3','7213')
go
insert into dbo.m_tb([m_id],m_div,m_dml,m_sea,m_sen) values('353','B','7','0','39')
go
insert into dbo.m_tb([m_id],m_div,m_dml,m_sea,m_sen) values('349','A','16','0','106')
go
insert into dbo.m_tb([m_id],m_div,m_dml,m_sea,m_sen) values('315','b','7','0','136')
go
select *from dbo.m_tb
---下面sql 可以得到你的结果:
select [t1].[m_div] as DIV,[t2].[m_dml] as DML ,[t3].[m_sea] as SEA,[t4].[m_sen] as SEN from dbo.m_tb t1,dbo.m_tb t2,dbo.m_tb t3,dbo.m_tb t4
where([t1].id=[t2].id and [t2].id=[t3].id and [t3].id=[t4].id )
/*
DIV 322 344 341 325 353 349 315
DML 1 56 1 23 7 6 7
SEA 0 3 0 25 0 0 0
SEN 20 7213 22 7109 39 106 136
*/
--行列互转
--1、行换列
if object_id('Class') is not null
drop table Class
Go
Create table Class(
[Student] nvarchar(2),
[Course] nvarchar(2),
[Score] int)
Insert Class
select N'张三',N'语文',78 union all
select N'张三',N'数学',87 union all
select N'张三',N'英语',82 union all
select N'张三',N'物理',90 union all
select N'李四',N'语文',65 union all
select N'李四',N'数学',77 union all
select N'李四',N'英语',65 union all
select N'李四',N'物理',85
Go
--2000方法:
--动态:
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([Course])+'=max(case when [Course]='
+quotename([Course],'''')+' then [Score] else 0 end)'
from
Class group by[Course]
--select @s
exec('select [Student]'+@s+' from Class group by [Student]')
--生成静态:
select
[Student],
[数学]=max(case when [Course]='数学' then [Score] else 0 end),
[物理]=max(case when [Course]='物理' then [Score] else 0 end),
[英语]=max(case when [Course]='英语' then [Score] else 0 end),
[语文]=max(case when [Course]='语文' then [Score] else 0 end)
from
Class
group by [Student]
GO
--动态:
declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]
select @s
exec('select * from Class pivot (max([Score]) for [Course] in('+@s+'))b')
--生成静态:
select *
from
Class
pivot
(max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b
--生成格式:
/*
Student 数学 物理 英语 语文
------- ----------- ----------- ----------- -----------
李四 77 85 65 65
张三 87 90 82 78
(2 行受影响)
*/
go
--加上总成绩(学科平均分)
--2000方法:
--动态:
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+'then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+',[总成绩]=sum([Score]) from Class group by [Student]')--加多一列(学科平均分用avg([Score]))
生成动态:
select
[Student],
[数学]=max(case when [Course]='数学' then [Score] else 0 end),
[物理]=max(case when [Course]='物理' then [Score] else 0 end),
[英语]=max(case when [Course]='英语' then [Score] else 0 end),
[语文]=max(case when [Course]='语文' then [Score] else 0 end),
[总成绩]=([Score]) --加多一列(学科平均分用avg([Score]))
from
Class
group by [Student]
go
--2005方法:
动态:
declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]
--isnull(@s+',','') 去掉字符串@s中第一个逗号
exec('select [Student],'+@s+',[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a
pivot (max([Score]) for [Course] in('+@s+'))b ')
--生成静态:
select
[Student],[数学],[物理],[英语],[语文],[总成绩]
from
(select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a --平均分时用avg([Score])
pivot
(max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b
生成格式:
/*
Student 数学 物理 英语 语文 总成绩
------- ----------- ----------- ----------- ----------- -----------
李四 77 85 65 65 292
张三 87 90 82 78 337
(2 行受影响)
*/
go
楼主标题太雷人了,当心被删了帖子