使用按功能分组 [英] Using Group by Functions

问题描述

亲爱的这一切都是我的数据表。我需要获得每个员工的基本薪酬，代码，旅行，电话，......尊重金额。使用功能分组

像这样......员工2：

代码：2

基础知识：15000

旅行：0

手机：1500

互联网：500

Busallo：0

Dear all this is my data table. i need to Get Each and every Employee BasicSalary,Code,Travel,Phone,... With respect Amount .using Group by Functions
Like this...Employe 2:
Code:2
Basicsalary:15000
Travel:0
phone:1500
Internet:500
Busallo:0

year	month	EmID    Component	Amount	Remarks
2013	3	2	Basicsal	15000	dsf
2013	3	2	CODE	         2	dsf
2013	3	2	Travel	         0	dsf
2013	3	2	Phone	        1500	dsf
2013	3	2	Internet	500	dsf
2013	3	2	Bus Allo	0	dsf
2013	3	2	BF	        0	dsf
2013	3	2	ESI	         0	dsf
2013	3	2	Medical	       -500	dsf
2013	3	2	Cross	        17000	dsf
2013	3	2	Net	        16500	dsf
2013	3	2	Basicsal	15000	ASD
2013	3	2	CODE	        2	ASD
2013	3	2	Travel	         0	ASD
2013	3	2	Phone	        1500	ASD
2013	3	2	Internet	500	ASD
2013	3	2	Bus Allo	0	ASD
2013	3	2	BF	        0	ASD
2013	3	2	ESI	         0	ASD
2013	3	2	Medical	       -500	ASD
2013	3	2	Cross	       17000	ASD
2013	3	2	Net	       16500	ASD
2013	3	2	Remarks	        16500	ASD
2013	3	3	Basicsal	12000	
2013	3	3	CODE	        3	
2013	3	3	Travel	        0	
2013	3	3	Phone	        0	
2013	3	3	Internet	0	
2013	3	3	Bus Allo	480	
2013	3	3	BF	        0	
2013	3	3	ESI	        0	
2013	3	3	Medical 	0	
2013	3	3	Cross	        12480	
2013	3	3	Net	         12480	
2013	3	3	Remarks	         12480	
2013	3	4	Basicsal	95579	
2013	3	4	CODE	        4	
2013	3	4	Travel	         2469	
2013	3	4	Phone	         3704	
2013	3	4	Internet	0	
2013	3	4	Bus Allo	0	
2013	3	4	BF	        -200	
2013	3	4	ESI	        0	
2013	3	4	Medical	           0	
2013	3	4	Cross	        101752	
2013	3	4	Net	        101552	
2013	3	4	Remarks	         101552

推荐答案

试试这样：



我不太确定Unpivot，所以你可以这样做：

Try like this:

I am not quite sure about Unpivot,so you can do like this format:

select * from Tablename
 declare @tab1e table(EmpId int,BasicSal int,Code int,Travel int,Phone int)

 insert into @tab1e(EmpId)
 select distinct EmpId from Tablename

update T set T.BasicSal=A.Amount from @tab1e T inner Join Tablename A on A.EmpId=T.EmpId where A.Component='BasicSal'
update T set T.Code=A.Amount from @tab1e T inner Join Tablename A on A.EmpId=T.EmpId where A.Component='Code'
update T set T.Travel=A.Amount from @tab1e T inner Join Tablename A on A.EmpId=T.EmpId where A.Component='Travel'
update T set T.Phone=A.Amount from @tab1e T inner Join Tablename A on A.EmpId=T.EmpId where A.Component='Phone'

select EmpId,BasicSal,Code,Travel,Phone from @tab1e

访问链接...

pivot查询

http://blogs.msdn.com/b/spike/archive/2009/03/03/pivot-tables-in-sql-server-a-simple-sample.aspx [< a href =http://blogs.msdn.com/b/spike/archive/2009/03/03/pivot-tables-in-sql-server-a-simple-sample.aspx\"target =_ blanktitle =新窗口> ^ ]

http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/ [ ^ ]



使用以下查询

visit link...
pivot query
http://blogs.msdn.com/b/spike/archive/2009/03/03/pivot-tables-in-sql-server-a-simple-sample.aspx[^]
http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/[^]

use below query

with a as
(select 2013 as year,   3 month ,   4 EmID    , 'Basicsal' Component    ,   95579 Amount        union all
select 2013,    3,  4,  'CODE'   ,       4  union all
select 2013,    3,  4,  'Travel'     ,        2469  union all
select 2013,    3,  4,  'Phone'  ,        3704  union all
select 2013,    3,  4,  'Internet', 0   union all
select 2013,    3,  4,  'Bus Allo'  ,0  union all
select 2013,    3,  4,  'BF'            ,-200   union all
select 2013,    3,  4,  'ESI'        ,0 union all
select 2013,    3,  4,  'Medical'       ,       0   union all
select 2013,    3,  4,  'Cross'    ,     101752 union all
select 2013,    3,  4,  'Net'          , 101552 union all
select 2013,    3,  4,  'Remarks'      ,      101552
)

select * from a
pivot (sum (Amount) for Component in ([Basicsal],[CODE],[Travel],[Phone],[Internet],[Bus Allo],[BF],[ESI],[Medical],[Cross],[Net],[Remarks])) as AvgIncomePerDay

快乐编码！

：）

Happy Coding!
:)

这篇关于使用按功能分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！