34,873
社区成员
发帖
与我相关
我的任务
分享求一sql语句
有数据如下：
员工代码 部门代码 ympdba ymzovr amount
4173 34 1120 N 0
4173 34 1140 N 2100
4173 34 1160 N 2100
4173 34 1180 N 2100
4173 34 1200 N 2100
4173 34 1280 N 2.5
4173 34 1300 N 0
4173 34 2000 N 0
4173 34 2020 N 0
4173 34 2040 N 0
4173 34 2080 N 8
4173 34 2100 N 2
4173 34 2120 N 0.5
4173 34 2140 N 8
4206 32 1120 N 0
4206 32 1140 N 4000
4206 32 1160 N 4000
4206 32 1180 N 4000
4206 32 1200 N 4000
4206 32 1220 N 150
4206 32 1280 N 2.5
4206 32 1300 N 0
4206 32 2000 N 0
4206 32 2020 N 0
4206 32 2040 N 0
4206 32 2080 N 8
4206 32 2100 N 2
4206 32 2120 N 0.5
4206 32 2140 N 8
希望能得到以下格式：
部门代码 员工代码 1120 1140 1160 1180 1200 1280 2080 2100 2120 2140
34 4173 0 2100 2100 2100 2100 2.50 8 2 0.5 8
32 4206 0 4000 4000 4000 4000 2.50 8 2 0.5 8
请教，谢谢
只有40分了，都送了
员工代码 部门代码 ympdba ymzovr amount
4173 34 1120 N 0
4173 34 1140 N 2100
4173 34 1160 N 2100
4173 34 1180 N 2100
4173 34 1200 N 2100
4173 34 1280 N 2.5
4173 34 1300 N 0
4173 34 2000 N 0
4173 34 2020 N 0
4173 34 2040 N 0
4173 34 2080 N 8
4173 34 2100 N 2
4173 34 2120 N 0.5
4173 34 2140 N 8
4206 32 1120 N 0
4206 32 1140 N 4000
4206 32 1160 N 4000
4206 32 1180 N 4000
4206 32 1200 N 4000
4206 32 1220 N 150
4206 32 1280 N 2.5
4206 32 1300 N 0
4206 32 2000 N 0
4206 32 2020 N 0
4206 32 2040 N 0
4206 32 2080 N 8
4206 32 2100 N 2
4206 32 2120 N 0.5
4206 32 2140 N 8
希望能得到以下格式：
部门代码 员工代码 1120 1140 1160 1180 1200 1280 2080 2100 2120 2140
34 4173 0 2100 2100 2100 2100 2.50 8 2 0.5 8
32 4206 0 4000 4000 4000 4000 2.50 8 2 0.5 8
请教，谢谢
只有40分了，都送了
...全文
请发表友善的回复…
发表回复
yanglinqiang 2009-12-07
- 打赏
- 举报
use csdn
go
if object_id('[tb]') is not null drop table [tb]
go
create table [tb](员工代码 int,部门代码 int,ympdba int, ymzovr varchar(1),amount float)
insert tb select '4173','34','1120','N','0 '
union all select '4173','34','1140','N','2100 '
union all select '4173','34','1160','N','2100 '
union all select '4173','34','1180','N','2100 '
union all select '4173','34','1200','N','2100'
union all select '4173','34','1280','N','2.5'
union all select '4173','34','1300','N','0 '
union all select '4173','34','2000','N','0 '
union all select '4173','34','2020','N','0 '
union all select '4173','34','2040','N','0 '
union all select '4173','34','2080','N','8 '
union all select '4173','34','2100','N','2 '
union all select '4173','34','2120','N','0.5'
union all select '4173','34','2140','N','8 '
union all select '4206','32','1120','N','0 '
union all select '4206','32','1140','N','4000 '
union all select '4206','32','1160','N','4000'
union all select '4206','32','1180','N','4000'
union all select '4206','32','1200','N','4000'
union all select '4206','32','1220','N','150'
union all select '4206','32','1280','N','2.5'
union all select '4206','32','1300','N','0'
union all select '4206','32','2000','N','0'
union all select '4206','32','2020','N','0'
union all select '4206','32','2040','N','0'
union all select '4206','32','2080','N','8'
union all select '4206','32','2100','N','2'
union all select '4206','32','2120','N','0.5'
union all select '4206','32','2140','N','8'
select 部门代码,员工代码
,[1120] as [1120]
,[1140] as [1140]
,[1160] as [1160]
,[1180] as [1180]
,[1200] as [1200]
,[1280] as [1280]
,[2080] as [2080]
,[2100] as [2100]
,[2120] as [2120]
,[2140] as [2140]
from tb pivot(sum(amount)
for ympdba in([1120],[1140],[1160],[1180],[1200],[1280],[2080],[2100],[2120],[2140]))
as pvt
/*结果
(29 行受影响)
部门代码 员工代码 1120 1140 1160 1180 1200 1280 2080 2100 2120 2140
----------- ----------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------
34 4173 0 2100 2100 2100 2100 2.5 8 2 0.5 8
32 4206 0 4000 4000 4000 4000 2.5 8 2 0.5 8
(2 行受影响)
*/
laker_914 2009-12-07
- 打赏
- 举报
又是行列转换问题
nianran520 2009-12-07
- 打赏
- 举报
declare @sql varchar(8000)
select @sql = ''
select @sql = @sql + ','+ ' max(case ympdba when '''+ympdba+''' then amount else 0 end) as '''+ympdba+'''' from tb
select @sql = 'select 部门代码,员工代码'+@sql+' from tb group by [部门代码],[员工代码]'
exec(@sql)nianran520 2009-12-07
- 打赏
- 举报
--> 测试数据：tb
if object_id('tb') is not null
drop table tb
create table tb([员工代码] varchar(20),[部门代码] varchar(20),[ympdba] varchar(20),[ymzovr] varchar(1),[amount] numeric(5,1))
insert tb
select 4173,34,1120,'N',0 union all
select 4173,34,1140,'N',2100 union all
select 4173,34,1160,'N',2100 union all
select 4173,34,1180,'N',2100 union all
select 4173,34,1200,'N',2100 union all
select 4173,34,1280,'N',2.5 union all
select 4173,34,1300,'N',0 union all
select 4173,34,2000,'N',0 union all
select 4173,34,2020,'N',0 union all
select 4173,34,2040,'N',0 union all
select 4173,34,2080,'N',8 union all
select 4173,34,2100,'N',2 union all
select 4173,34,2120,'N',0.5 union all
select 4173,34,2140,'N',8 union all
select 4206,32,1120,'N',0 union all
select 4206,32,1140,'N',4000 union all
select 4206,32,1160,'N',4000 union all
select 4206,32,1180,'N',4000 union all
select 4206,32,1200,'N',4000 union all
select 4206,32,1220,'N',150 union all
select 4206,32,1280,'N',2.5 union all
select 4206,32,1300,'N',0 union all
select 4206,32,2000,'N',0 union all
select 4206,32,2020,'N',0 union all
select 4206,32,2040,'N',0 union all
select 4206,32,2080,'N',8 union all
select 4206,32,2100,'N',2 union all
select 4206,32,2120,'N',0.5 union all
select 4206,32,2140,'N',8
declare @sql varchar(8000)
select @sql = ''
select @sql = @sql + ','+ ' max(case ympdba when '''+ympdba+''' then amount else 0 end) as ympdba' from tb
select @sql = 'select 部门代码,员工代码'+@sql+' from tb group by [部门代码],[员工代码]'
exec(@sql)
----------------------------------
34 4173 .0 2100.0 2100.0 2100.0 2100.0 2.5 .0 .0 .0 .0 8.0 2.0 .5 8.0 .0 2100.0 2100.0 2100.0 2100.0 .0 2.5 .0 .0 .0 .0 8.0 2.0 .5 8.0
32 4206 .0 4000.0 4000.0 4000.0 4000.0 2.5 .0 .0 .0 .0 8.0 2.0 .5 8.0 .0 4000.0 4000.0 4000.0 4000.0 150.0 2.5 .0 .0 .0 .0 8.0 2.0 .5 8.0
百年树人 2009-12-07
- 打赏
- 举报
---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([员工代码] int,[部门代码] int,[ympdba] int,[ymzovr] varchar(1),[amount] numeric(5,1))
insert [tb]
select 4173,34,1120,'N',0 union all
select 4173,34,1140,'N',2100 union all
select 4173,34,1160,'N',2100 union all
select 4173,34,1180,'N',2100 union all
select 4173,34,1200,'N',2100 union all
select 4173,34,1280,'N',2.5 union all
select 4173,34,1300,'N',0 union all
select 4173,34,2000,'N',0 union all
select 4173,34,2020,'N',0 union all
select 4173,34,2040,'N',0 union all
select 4173,34,2080,'N',8 union all
select 4173,34,2100,'N',2 union all
select 4173,34,2120,'N',0.5 union all
select 4173,34,2140,'N',8 union all
select 4206,32,1120,'N',0 union all
select 4206,32,1140,'N',4000 union all
select 4206,32,1160,'N',4000 union all
select 4206,32,1180,'N',4000 union all
select 4206,32,1200,'N',4000 union all
select 4206,32,1220,'N',150 union all
select 4206,32,1280,'N',2.5 union all
select 4206,32,1300,'N',0 union all
select 4206,32,2000,'N',0 union all
select 4206,32,2020,'N',0 union all
select 4206,32,2040,'N',0 union all
select 4206,32,2080,'N',8 union all
select 4206,32,2100,'N',2 union all
select 4206,32,2120,'N',0.5 union all
select 4206,32,2140,'N',8
---查询---
declare @sql varchar(8000)
select
@sql=isnull(@sql+',','')+'sum(case when ympdba='+ympdba+' then amount else 0 end) as ['+ympdba+']'
from
(select distinct ltrim(ympdba) as ympdba from tb) t
set @sql='select 员工代码,部门代码,'+@sql+' from tb group by 员工代码,部门代码'
exec (@sql)
---结果---
员工代码 部门代码 1120 1140 1160 1180 1200 1220 1280 1300 2000 2020 2040 2080 2100 2120 2140
----------- ----------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
4206 32 .0 4000.0 4000.0 4000.0 4000.0 150.0 2.5 .0 .0 .0 .0 8.0 2.0 .5 8.0
4173 34 .0 2100.0 2100.0 2100.0 2100.0 .0 2.5 .0 .0 .0 .0 8.0 2.0 .5 8.0
ChinaJiaBing 2009-12-07
- 打赏
- 举报
行转列转换，在精华区里找找
ChinaJiaBing 2009-12-07
- 打赏
- 举报
行转列转换，在精化区里找找
快乐_石头 2009-12-07
- 打赏
- 举报
[Quote=引用 1 楼 jiangshun 的回复:]
行转列，小Ｆ上
[/Quote]
.
行转列，小Ｆ上
[/Quote]
.
gbys2 2009-12-07
- 打赏
- 举报
员工代码 部门代码 ympdba ymzovr amount
4173 34 1120 N 0
4173 34 1140 N 2100
4173 34 1160 N 2100
4173 34 1180 N 2100
4173 34 1200 N 2100
4173 34 1280 N 2.5
4173 34 1300 N 0
4173 34 2000 N 0
4173 34 2020 N 0
4173 34 2040 N 0
4173 34 2080 N 8
4173 34 2100 N 2
4173 34 2120 N 0.5
4173 34 2140 N 8
4206 32 1120 N 0
4206 32 1140 N 4000
4206 32 1160 N 4000
4206 32 1180 N 4000
4206 32 1200 N 4000
4206 32 1220 N 150
4206 32 1280 N 2.5
4206 32 1300 N 0
4206 32 2000 N 0
4206 32 2020 N 0
4206 32 2040 N 0
4206 32 2080 N 8
4206 32 2100 N 2
4206 32 2120 N 0.5
4206 32 2140 N 8
更清楚些
4173 34 1120 N 0
4173 34 1140 N 2100
4173 34 1160 N 2100
4173 34 1180 N 2100
4173 34 1200 N 2100
4173 34 1280 N 2.5
4173 34 1300 N 0
4173 34 2000 N 0
4173 34 2020 N 0
4173 34 2040 N 0
4173 34 2080 N 8
4173 34 2100 N 2
4173 34 2120 N 0.5
4173 34 2140 N 8
4206 32 1120 N 0
4206 32 1140 N 4000
4206 32 1160 N 4000
4206 32 1180 N 4000
4206 32 1200 N 4000
4206 32 1220 N 150
4206 32 1280 N 2.5
4206 32 1300 N 0
4206 32 2000 N 0
4206 32 2020 N 0
4206 32 2040 N 0
4206 32 2080 N 8
4206 32 2100 N 2
4206 32 2120 N 0.5
4206 32 2140 N 8
更清楚些
jiangshun 2009-12-07
- 打赏
- 举报
行转列，小Ｆ上
--小F-- 2009-12-07
- 打赏
- 举报
--行列互转
/******************************************************************************************************************************************************
以学生成绩为例子，比较形象易懂
整理人：中国风(Roy)
日期:2008.06.06
******************************************************************************************************************************************************/
--1、行互列
--> --> (Roy)生成測試數據
if not object_id('Class') is 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]
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]
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),
[总成绩]=sum([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
--2、列转行
--> --> (Roy)生成測試數據
if not object_id('Class') is null
drop table Class
Go
Create table Class([Student] nvarchar(2),[数学] int,[物理] int,[英语] int,[语文] int)
Insert Class
select N'李四',77,85,65,65 union all
select N'张三',87,90,82,78
Go
--2000:
动态:
declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+'select [Student],[Course]='+quotename(Name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第一个union all
+',[Score]='+quotename(Name)+' from Class'
from syscolumns where ID=object_id('Class') and Name not in('Student')--排除不转换的列
order by Colid
exec('select * from ('+@s+')t order by [Student],[Course]')--增加一个排序
生成静态:
select *
from (select [Student],[Course]='数学',[Score]=[数学] from Class union all
select [Student],[Course]='物理',[Score]=[物理] from Class union all
select [Student],[Course]='英语',[Score]=[英语] from Class union all
select [Student],[Course]='语文',[Score]=[语文] from Class)t
order by [Student],[Course]
go
--2005:
动态:
declare @s nvarchar(4000)
select @s=isnull(@s+',','')+quotename(Name)
from syscolumns where ID=object_id('Class') and Name not in('Student')
order by Colid
exec('select Student,[Course],[Score] from Class unpivot ([Score] for [Course] in('+@s+'))b')
go
select
Student,[Course],[Score]
from
Class
unpivot
([Score] for [Course] in([数学],[物理],[英语],[语文]))b
生成格式:
/*
Student Course Score
------- ------- -----------
李四 数学 77
李四 物理 85
李四 英语 65
李四 语文 65
张三 数学 87
张三 物理 90
张三 英语 82
张三 语文 78
(8 行受影响)
*/
