how to use cross tab query

Posted by Amritha444 under Sql Server on 6/24/2011 | Points: 10 | Views : 1931 | Status : [Member] | Replies : 3
Hi all

I want to know how to use cross tab query

I wnat pepare monthly statement of slary for employees..for that i want to join employee table and allowance table
in employee table

EmpId Employeename
1 Amrutha
2 Anisha
3 Anusha
in allowance table

Id Headcode
1 BP
2 HRA
3 DA
4 CA

i want report in format

empID Employeename BP HRA DA ..

How to do it using cross tab query...


Thanks In Advance
Amrutha




Responses

Posted by: PandianS on: 6/24/2011 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi

You can use PIVOT or UNPIVOT

Cheers
www.sqlserverbuddy.blogspot.com

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Amritha444, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriramnandha on: 5/20/2012 [Member] Starter | Points: 25

Up
0
Down
CROSS TAB U CAN USE CROSS JOIN

SELECT * FROM TABLE1 CROSS JOIN TABLE2

REGARDS


sriram

Amritha444, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Meet1424 on: 5/20/2012 [Member] Starter | Points: 25

Up
0
Down
I was facing same issue and I solved it bt there is a little diff in your n my records like in my employee table there was a column headcode and there were multiple entries of an employee with same id in this table as

EID EmpName HeadCode Amount
1 ABC HRA 1500
1 ABC BP 200
2 DEF BP 1000

and more...
and there was second table for all headcodes as in your structure.
i use pivot n my output was

EID ENAME HRA BP DA ....

this is the procedure which gives me output i want

create procedure exec[dbo].[sp_GetEmployees]


as
declare @temp nvarchar(MAX),@sql nvarchar(MAX),
@headcode nvarchar(MAX)
begin
set @temp=''
select @temp=@temp+headcode+',' from allowance
select @headcode= SUBSTRING(@temp,0,len(@temp))

set @sql =N'select ename,'+@headcode+ N' from (select eid,headcode,ename,amount from employees) as p pivot (max(amount) for [headcode] in('+@headcode + N'))as pvt order by ename asc '





exec (@sql)

end




Amritha444, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response