Displaying Data as Column alias

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Displaying Data as Column alias

Author

Topic

ArchanaSrk
Starting Member

1 Post

Posted - 2009-02-03 : 07:25:20

Hi...
Anyone kindly help me....
I have the following Tables

LeaveTypeMaster
LeaveTypeId LeaveTypeName
1 Casual Leave
2 Sick Leave
LeaveMaster
EmployeeId LeaveTypeId LeaveBalance(in days)
A001 1 6
A002 1 3
A001 2 4
A002 2 5

I need to select the data as

EmployeeId Casual Leave Sick Leave
A001 6 4
A002 3 5

That is I need to us the LeaveTypeName a Column alias
Please Help me for this

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-03 : 07:35:40

declare @LeaveTypeMaster table(LeaveTypeId int, LeaveTypeName varchar(32))
insert into @LeaveTypeMaster select 1, 'Casual Leave' union all select
2, 'Sick Leave'
declare @LeaveMaster table(EmployeeId varchar(32),LeaveTypeId int,LeaveBalance int)
insert into @LeaveMaster select 'A001', 1, 6 union all
select 'A002', 1, 3 union all
select 'A001', 2, 4 union all
select 'A002', 2, 5

select employeeid ,max(case when leavetypename = 'Casual Leave' then leavebalance end) as 'Casual Leave',
max(case when leavetypename = 'Sick Leave' then leavebalance end) as 'Sick Leave'
from (select employeeid, leavetypename, leavebalance
from @LeaveTypeMaster l
inner join @LeaveMaster m on m.LeaveTypeId = l.LeaveTypeId) t
group by employeeid

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-03 : 07:37:49

by using pivot
select employeeid ,[Casual Leave] as 'CasualLeave' , [Sick Leave]as 'SickLeave'
from (select employeeid, leavetypename, leavebalance
from @LeaveTypeMaster l
inner join @LeaveMaster m on m.LeaveTypeId = l.LeaveTypeId)t
pivot (max(leavebalance)for leavetypename in( [Casual Leave] , [Sick Leave]))pvt

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-03 : 07:45:15

Try This also


declare @temp table (EmployeeId int, LeaveTypeId int,LeaveBalance int)
insert into @temp 
select 1, 1, 6 union all
select 2, 1, 3 union all
select 1, 2, 4 union all
select 2, 2, 5


select employeeid,
max( case when leavetypeid = 1 then LeaveBalance end)'CasualLeave',
max(case when leavetypeid = 2 then LeaveBalance end) 'SickLeave'
 from @temp
group by employeeid

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-03 : 23:12:49

declare @temp table (EmployeeId int, LeaveTypeId int,
LeaveBalance int)
insert into @temp
select 1, 1, 6 union all
select 2, 1, 3 union all
select 1, 2, 4 union all
select 2, 2, 5

declare @LeaveTypeMaster table(LeaveTypeId int,
LeaveTypeName varchar(32))
insert into @LeaveTypeMaster
select 1, 'Casual Leave' union all
select 2, 'Sick Leave'

select employeeid,
max( case when l.LeaveTypeName = 'Casual Leave' then t.LeaveBalance end) AS 'Casual Leave' ,
max( case when l.LeaveTypeName = 'Sick Leave' then t.LeaveBalance end) AS 'Sick Leave'
from @temp t
inner join @LeaveTypeMaster l on l.leavetypeid = t.leavetypeid
group by t.employeeid

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources