How to get row value as Header

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

How to get row value as Header

Author

Topic

naresh0407
Starting Member

30 Posts

Posted - 2010-11-18 : 07:35:21

Hi All,

This is my table and query.When i run the query i am getting EmpName,Dates and Time.

Now i want Dates as header and corresponding Time for that date as row value.

I am trying to get reports like this

EmpName 10/3/2010 10/4/2010 10/5/2010 10/6/2010 10/7/2010 10/8/2010 10/9/201

Naresh 17:00 14:00 ..........

CREATE TABLE TestTable(EmpNo int,EmpName varchar(20),
atndate DATETIME,
StartTime varchar(50),EndTime varchar(50),MatchingOff varchar(20),CampaignID int
)

SET IDENTITY_INSERT mytable ON
Insert into TestTable(EmpNo,EmpName,atndate,StartTime,EndTime,MatchingOff,CampaignID)

select '3','NareshNadimpalli','10/3/2010','0','0','Off','3' union all
select '3','NareshNadimpalli','10/4/2010','7:30 AM','6:00 PM','07:30 ','3' union all
select '3','NareshNadimpalli','10/5/2010','0','0','Off','3' union all
select '3','NareshNadimpalli','10/6/2010','7:30 AM','7:30 AM','07:30 ','3' union all
select '3','NareshNadimpalli','10/7/2010','7:30 AM','7:30 AM','07:30 ','3' union all
select '3','NareshNadimpalli','10/8/2010','7:30 AM','7:30 AM','07:30 ','3' union all
select '3','NareshNadimpalli','10/9/2010','7:30 AM','7:30 AM','07:30 ','3' union all

select '2','ALETARIDINGER','10/3/2010 12:00:00 AM','0','0','Off','1' union all
select '2','ALETARIDINGER','10/4/2010 12:00:00 AM','7:30 AM','6:00 PM','07:30 ','1' union all
select '2','ALETARIDINGER','10/5/2010 12:00:00 AM','0','0','Off','1' union all
select '2','ALETARIDINGER','10/6/2010 12:00:00 AM','7:30 AM','7:30 AM','07:30 ','1' union all
select '2','ALETARIDINGER','10/7/2010 12:00:00 AM','7:30 AM','7:30 AM','07:30 ','1' union all
select '2','ALETARIDINGER','10/8/2010 12:00:00 AM','7:30 AM','7:30 AM','07:30 ','1' union all
select '2','ALETARIDINGER','10/9/2010 12:00:00 AM','7:30 AM','7:30 AM','07:30 ','1' union all

select '2','ALETARIDINGER','10/17/2010 12:00:00 AM','0','0','Off','1' union all
select '2','ALETARIDINGER','10/18/2010 12:00:00 AM','7:30 AM','6:00 PM','07:30 ','1' union all
select '2','ALETARIDINGER','10/19/2010 12:00:00 AM','0','0','Off','1' union all
select '2','ALETARIDINGER','10/20/2010 12:00:00 AM','7:30 AM','7:30 AM','07:30 ','1' union all
select '2','ALETARIDINGER','10/21/2010 12:00:00 AM','7:30 AM','7:30 AM','07:30 ','1' union all
select '2','ALETARIDINGER','10/22/2010 12:00:00 AM','7:30 AM','7:30 AM','07:30 ','1' union all

select '2','ALETARIDINGER','10/23/2010 12:00:00 AM','7:30 AM','7:30 AM','07:30 ','1'

Select empno,empname,atndate,starttime +'-'+Endtime as EmpTime from TestTable
where atndate>='09/26/2010' and atndate<='10/16/2010' and matchingoff<>'Off'
and empno not in
(Select empno from TestTable where atndate between '10/17/2010' and '10/24/2010'
and matchingoff='Off' group by empno)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-18 : 07:46:33

see
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources