CrossTab Query - SQL Server Forums

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

CrossTab Query

Author

Topic

Jabez
Starting Member

19 Posts

Posted - 2007-09-17 : 13:29:56

Hi,

Currently working on a Attendance System project .
Iam storing the data in table against the employee code, date and status (basically a rowwise data).

Would require a query which can generate a cross-tab display with Employee Codes (on X axis), Dates (on Y-Axis) and with the Attendance Status.

The sample data for the same is as follows:

EmpCode Att_Date Att_Status
------- -------- ----------
001 01/01/2007 P
001 01/02/2007 A
.. ... ..
001 01/31/2007 P

002 01/01/2007 P
. . .
. . .
. . .

Would require the output as..

EmpCode 01/01/2007 01/02/2007 ...... 01/31/2007
------- ---------- ---------- ----------
001 P A P
002 A P P
. . . .
. . . .
. . . .

Thanking you in anticipation.

Jabez.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-17 : 14:09:35

Use this

SELECT		EmpCode,
		MAX(CASE WHEN theDay = 1 THEN Att_Status ELSE '' END) AS [1],
		MAX(CASE WHEN theDay = 2 THEN Att_Status ELSE '' END) AS [2],
		MAX(CASE WHEN theDay = 3 THEN Att_Status ELSE '' END) AS [3],
		MAX(CASE WHEN theDay = 4 THEN Att_Status ELSE '' END) AS [4],
		MAX(CASE WHEN theDay = 5 THEN Att_Status ELSE '' END) AS [5],
		MAX(CASE WHEN theDay = 6 THEN Att_Status ELSE '' END) AS [6],
		MAX(CASE WHEN theDay = 7 THEN Att_Status ELSE '' END) AS [7],
		MAX(CASE WHEN theDay = 8 THEN Att_Status ELSE '' END) AS ,
		MAX(CASE WHEN theDay = 9 THEN Att_Status ELSE '' END) AS [9],
		MAX(CASE WHEN theDay = 10 THEN Att_Status ELSE '' END) AS [10],
		MAX(CASE WHEN theDay = 11 THEN Att_Status ELSE '' END) AS [11],
		MAX(CASE WHEN theDay = 12 THEN Att_Status ELSE '' END) AS [12],
		MAX(CASE WHEN theDay = 13 THEN Att_Status ELSE '' END) AS [13],
		MAX(CASE WHEN theDay = 14 THEN Att_Status ELSE '' END) AS [14],
		MAX(CASE WHEN theDay = 15 THEN Att_Status ELSE '' END) AS [15],
		MAX(CASE WHEN theDay = 16 THEN Att_Status ELSE '' END) AS [16],
		MAX(CASE WHEN theDay = 17 THEN Att_Status ELSE '' END) AS [17],
		MAX(CASE WHEN theDay = 18 THEN Att_Status ELSE '' END) AS [18],
		MAX(CASE WHEN theDay = 19 THEN Att_Status ELSE '' END) AS [19],
		MAX(CASE WHEN theDay = 20 THEN Att_Status ELSE '' END) AS [20],
		MAX(CASE WHEN theDay = 21 THEN Att_Status ELSE '' END) AS [21],
		MAX(CASE WHEN theDay = 22 THEN Att_Status ELSE '' END) AS [22],
		MAX(CASE WHEN theDay = 23 THEN Att_Status ELSE '' END) AS [23],
		MAX(CASE WHEN theDay = 24 THEN Att_Status ELSE '' END) AS [24],
		MAX(CASE WHEN theDay = 25 THEN Att_Status ELSE '' END) AS [25],
		MAX(CASE WHEN theDay = 26 THEN Att_Status ELSE '' END) AS [26],
		MAX(CASE WHEN theDay = 27 THEN Att_Status ELSE '' END) AS [27],
		MAX(CASE WHEN theDay = 28 THEN Att_Status ELSE '' END) AS [28],
		MAX(CASE WHEN theDay = 29 THEN Att_Status ELSE '' END) AS [29],
		MAX(CASE WHEN theDay = 30 THEN Att_Status ELSE '' END) AS [30],
		MAX(CASE WHEN theDay = 31 THEN Att_Status ELSE '' END) AS [31]
FROM		(
			SELECT	EmpCode,
				DATEPART(DAY, Att_Date) AS theDay,
				Att_Status
			FROM	Table1
		) AS d
GROUP BY	EmpCode
ORDER BY	EmpCode

and rename the columns in your user application.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-18 : 01:41:33

Read about Cross-tab reports in sql server help file for more informations

Madhivanan

Failing to plan is Planning to fail

Jabez
Starting Member

19 Posts

Posted - 2007-09-18 : 02:03:45

Thanks for the quick response.

but how do I specify the dates dynamically (i.e the cycle is not always from 1 to 31 it could be from 10th to 9th)

Sorry for not putting it clear in the first post.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-18 : 02:22:43

We are sorry too...

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-18 : 04:08:07

Try this
http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx

Madhivanan

Failing to plan is Planning to fail

Jabez
Starting Member

19 Posts

Posted - 2007-09-18 : 08:17:22

I have implemented a crosstab sp to achieve the result.
The crosstab sp works fine for record of 50 employees and for a full cycle. Goes for a toss for more employees & full cycle as the statement exceeds the maximum allowed limit.

The problem I am facing now is with the size of a variable declared in SQL (varchar (8000)). If my where string exceeds more than 8000 characters then the sp doesn't work.

Is there a way to win over the limit size of a variable in SQL.

Below is the stored procedure (sp) I have used (forgive me):

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

ALTER PROCEDURE [dbo].[OAS_MonthlyPAXTab] @XField varChar(20), @XTable varChar(20),
@XWhereString varChar(8000), @XFunction varChar(10), @XFunctionField varChar(20), @XRow varchar(40)

AS
Declare @SqlStr nvarchar(4000)
Declare @tempsql nvarchar(4000)
Declare @SqlStrCur nvarchar(4000)
Declare @col nvarchar(100)

set @SqlStrCur = N'Select [' + @XField + '] into ##temptbl_Cursor from [' + @XTable + '] ' + @XWhereString + ' Group By [' + @XField + '] Order by [' + @XField + ']'

/* select @sqlstrcur */
exec sp_executesql @sqlstrcur

declare xcursor Cursor for Select * from ##temptbl_Cursor

open xcursor

Fetch next from xcursor
into @Col

While @@Fetch_Status = 0
Begin
set @Sqlstr = @Sqlstr + ", "
set @tempsql = isnull(@sqlstr,'') + isnull(@XFunction + '( Case When ' + @XField + " = '" +@Col +
-- "' then [" + @XFunctionField + "] Else '' End) As [" + @XFunction + Convert(varchar,Day(@Col)) + '-' + Convert(varchar,month(@Col)) + '-' + Convert(varchar,year(@Col)) + "]" ,'')
"' then [" + @XFunctionField + "] Else '' End) As [" + Convert(varchar,Day(@Col)) + '-' + Convert(varchar,month(@Col)) + '-' + Convert(varchar,year(@Col)) + "]" ,'')
set @Sqlstr = @tempsql

Fetch next from xcursor into @Col

End

--Print @tempsql

/* Select @Sqlstr as [mk], len(@sqlstr) as [leng] */

IF EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_NAME='MonthlyPADetails')
begin
drop table MonthlyPADetails
end

set @tempsql = 'Select ' + @XRow + ' as Code' + ' , ' + ' (ed_firstname + '' '' + ed_middlename + '' '' + ed_lastname) as [Name] ' + ', ' + @Sqlstr + ' Into MonthlyPADetails From ' + @XTable + ', ' + 'reqrec_employeedetails' +
@XWhereString + ' and MPAD_EmpCode = ed_empcode' + ' Group by ' + @XRow + ' , ' + ' ed_firstname' + ' , ed_middlename' + ' , ed_lastname'
-- + ',' + @XField + ',' + @XFunctionField

set @Sqlstr = @tempsql

Print @tempsql

Close xcursor
Deallocate xcursor

set @tempsql = N'Drop Table ##temptbl_Cursor'
exec sp_executesql @tempsql

/* Select @Sqlstr as [mk], len(@sqlstr) as [leng] */

exec sp_executesql @Sqlstr

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Thanks Again.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-18 : 08:22:13

If you are using SQL Server 2005, you can replace the old limit VARCHAR(8000) with the new VARCHAR(MAX) datatype.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-18 : 09:05:37

Or you could read this article
http://www.sqlservercentral.com/columnists/plarsson/pivottableformicrosoftsqlserver.asp

E 12°55'05.25"
N 56°04'39.16"

Jabez
Starting Member

19 Posts

Posted - 2007-09-18 : 10:02:41

Thanks madhivanan and Peso. Was able to solve using .NET crosstab.

Referred the link given by Madhivanan.

Thanks Again.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-18 : 12:14:33

quote:
Originally posted by Jabez

Thanks madhivanan and Peso. Was able to solve using .NET crosstab.

Referred the link given by Madhivanan.

Thanks Again.

Glad that you got it

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources