How to get row value as column header

Question

How to get row value as column header

naresh0407 93367

SSC-Addicted

Points: 479
More actions
November 18, 2010 at 6:30 am

#157323

Hi,
This is my data.
I am running this query i am getting result.
Select empname,atndate,starttime +'-'+Endtime as EmpTime from TestTable
where atndate>='09/26/2010' and atndate<='10/16/2010'
and empname not in
(Select empname from TestTable where atndate>= '10/17/2010' and atndate<='10/24/2010'
group by empname)
Same query i implemented in stored procedure i am not getting result nor errro.Simply i am getting column headers.
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'
Stored Procedure
exec Test '9/26/2010',1
alter PROCEDURE [dbo].[Test](@date1 DATETIME,@CampaignID int)
AS
DECLARE @query VARCHAR(MAX)
Declare @date11 datetime
Declare @date12 datetime
Declare @date13 datetime
Declare @date14 datetime
Declare @date15 datetime
Declare @date16 datetime
Declare @date17 datetime
Declare @date18 datetime
Declare @date19 datetime
set @date11='9/26/2010'
set @date12='9/27/2010'
set @date13='9/28/2010'
set @date14='9/29/2010'
set @date15='9/30/2010'
set @date16='10/1/2010'
set @date17='10/16/2010'
set @date18='10/17/2010'
set @date19='10/24/2010'
BEGIN
SET @query = 'SELECT [' + CONVERT(VARCHAR,@date11,101) + '], [' + CONVERT(VARCHAR,@date12,101) + '], [' + CONVERT(VARCHAR,@date13,101) + '], [' + CONVERT(VARCHAR,@date14,101) + '], [' + CONVERT(VARCHAR,@date15,101) + '], [' + CONVERT(VARCHAR,@date16,101)+ '], [' + CONVERT(VARCHAR,@date17,101) + ']' +
' FROM
(Select empname,atndate,startTime from TestTable where atndate>=' + CONVERT(VARCHAR,@date11,101) + ' and
atndate<=' + CONVERT(VARCHAR,@date17,101) + ' and empname not in
(Select empname from TestTable where atndate >=' + CONVERT(VARCHAR,@date18,101) + ' and atndate<=' + CONVERT(VARCHAR,@date19,101) + '
group by empname)
)
AS SourceTable
PIVOT
(
max(startTime)
FOR atndate IN (' + '[' + CONVERT(VARCHAR,@date11,101) + '], [' + CONVERT(VARCHAR,@date12,101)+ '], [' + CONVERT(VARCHAR,@date13,101)+ '], [' + CONVERT(VARCHAR,@date14,101)+ '], [' + CONVERT(VARCHAR,@date15,101)+ '], [' + CONVERT(VARCHAR,@date16,101)+ '],[' + CONVERT(VARCHAR,@date17,101) + ']' + ')
) AS PivotTable'
exec(@query)
END

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Take a look at pivot and unpivot, see if they'll do what you need.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

naresh0407 93367 SSC-Addicted Points: 479 More actions · Answer 2

Hi,

I trying with pivot but getting error.

Msg 156, Level 15, State 1, Line 7

Incorrect syntax near the keyword 'where'.he

I am trying to get reports like in excel sheet with empname,Date1,date2...Date7 as column Headers and Time as corresponding values for Date1 to Date7 and rows employeename

alter PROCEDURE [dbo].[Test](@date1 DATETIME,@CampaignID int)

AS

DECLARE @query VARCHAR(MAX)

Declare @date11 datetime

Declare @date12 datetime

Declare @date13 datetime

Declare @date14 datetime

Declare @date15 datetime

Declare @date16 datetime

Declare @date17 datetime

set @date11='9/26/2010'

set @date12='9/27/2010'

set @date13='9/28/2010'

set @date14='9/29/2010'

set @date15='9/30/2010'

set @date16='10/1/2010'

set @date17='10/2/2010'

BEGIN

SET @query = 'SELECT '+ CHAR(39) + 'Agents Scheduled as per Schedule' + CHAR(39) + ' AS HeadCount, [' + CONVERT(VARCHAR,@date11,101) + '], [' + CONVERT(VARCHAR,@date12,101) + '], [' + CONVERT(VARCHAR,@date13,101) + '], [' + CONVERT(VARCHAR,@date14,101) + '], [' + CONVERT(VARCHAR,@date15,101) + '], [' + CONVERT(VARCHAR,@date16,101)+ '], [' + CONVERT(VARCHAR,@date17,101) + ']' +

' FROM

(Select empno,empname,atndate,startTime from TestTable

where atndate>=[' + CONVERT(VARCHAR,'9/26/2010',101) + '] and atndate<=[' + CONVERT(VARCHAR,'10/2/2010',101) + ']

and empno not in

(Select empno from TestTable where atndate between [' + CONVERT(VARCHAR,'10/17/2010',101) + '] and [' + CONVERT(VARCHAR,'10/23/2010',101) + ']

group by empno)

where CampaignID=' + CAST(@CampaignID AS VARCHAR) + ')

AS SourceTable

PIVOT

(

max(startTime)

FOR atndate IN (' + '[' + CONVERT(VARCHAR,@date11,101) + '], [' + CONVERT(VARCHAR,@date12,101)+ '], [' + CONVERT(VARCHAR,@date13,101)+ '], [' + CONVERT(VARCHAR,@date14,101)+ '], [' + CONVERT(VARCHAR,@date15,101)+ '], [' + CONVERT(VARCHAR,@date16,101)+ '],[' + CONVERT(VARCHAR,@date17,101) + ']' + ')

) AS PivotTable'

print(@query)

END