how to use cross tab query

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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

0	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

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

Latest Posts