Using Group by Functions

Question

0.00/5 (No votes)

See more:

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

Posted 11-Mar-13 20:42pm

DhananjayanP

Updated 11-Mar-13 20:48pm

Aarti Meswania

v3

Add a Solution

Comments

Aarti Meswania 12-Mar-13 2:50am

respect amt means? percentage?

DhananjayanP 12-Mar-13 3:01am

no in the Component column having all the Allowances (Phone,Internet,Bus Allo,Cross,Net)like that i want split the component

Aarti Meswania 12-Mar-13 3:07am

do you want o/p like this?
year month EmID Basicsal CODE Travel Phone Internet ...
2013 3 2 15000 2 0 1500 500 ....

or...

summary of basicsal, internet , phone, travel for all employees?

DhananjayanP 12-Mar-13 3:09am

Yes i need
year month EmID Basicsal CODE Travel Phone Internet ...
2013 3 2 15000 2 0 1500 500 ... like this.Aarti Meswania

Davidduraisamy 12-Mar-13 3:10am

Explain how you want the ouptut..it was not clear

DhananjayanP 12-Mar-13 3:13am

Year month EmpId Code Basicsalary Travel Phone Internet Bus Allo BF
2013 3 2 2 1500 0 1500 500 0 -500
i need out put like this

Aarti Meswania 12-Mar-13 3:17am

okay it is called pivot table

DhananjayanP 12-Mar-13 3:28am

if it's possible to show Christel reports

Aarti Meswania 12-Mar-13 3:32am

hmm for that use crosstab report

Davidduraisamy 12-Mar-13 3:19am

Aarti whether it is possible to do with pivot.

Aarti Meswania 12-Mar-13 3:31am

yes it is possible

DhananjayanP 12-Mar-13 3:39am

How to add summary of basicsal, internet , phone, travel for Separate employees?

Aarti Meswania 12-Mar-13 3:41am

if pivot table query then see solution 2

or asking about crosstab?

Davidduraisamy 12-Mar-13 3:32am

how,In his statement there is no aggregate function then how u will do in Pivot

DhananjayanP 12-Mar-13 3:41am

How?

2 solutions

Solution 1

Try like this:

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

SQL

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

Posted 11-Mar-13 21:17pm

Davidduraisamy

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Aarti Meswania · Accepted Answer · 2013-03-11T21:18:00

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

SQL

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!
:)