Tuesday, September 25, 2007

PIVOT in SQL Server

CREATE PROCEDURE [dbo].[spLoadEmpFiles]
@FromDate SMALLDATETIME = NULL,
@ToDate SMALLDATETIME = NULL,
@EmployeeCode NVARCHAR(50) = '',
@EmployeeName NVARCHAR(255) = '',
@CompanyName NVARCHAR(255) = '',
@DepartmentName NVARCHAR(255) = '',
@DeptUnitName NVARCHAR(255) = '',
@JobTitleName NVARCHAR(255) = '',
@TeamName NVARCHAR(255) = '',
@Gender NVARCHAR(1) = ''
AS
BEGIN
DECLARE @HRFileID INT
DECLARE @HRFileName NVARCHAR(50)

DECLARE Cur CURSOR FOR
SELECT HRFileID, HRFileName
FROM dbo.tblHRFile

OPEN Cur
FETCH FROM Cur
INTO @HRFileID, @HRFileName
DECLARE @strSQL NVARCHAR(MAX)
DECLARE @params NVARCHAR(MAX)

SET @strSQL = 'SELECT t1.EmployeeID, t1.EmployeeCode, t1.EmployeeName
, t1.Gender
, t4.CompanyName
, t5.DepartmentName
, t6.DeptUnitName
, t7.JobTitleName
, t8.TeamName '
WHILE @@FETCH_STATUS = 0
BEGIN
SET @strSQL = @strSQL +', MAX(CASE WHEN t3.HRFileID = ' + CAST(@HRFileID AS VARCHAR) + ' Then ''x'' ELSE '''' END) AS [' + @HRFileName + ']'

FETCH FROM Cur
INTO @HRFileID, @HRFileName
END
CLOSE Cur
DEALLOCATE Cur

SET @strSQL = @strSQL + ' FROM dbo.tblEmployee t1
LEFT JOIN dbo.fnAppoint(@ToDate) t2 ON t1.EmployeeID = t2.EmployeeID
LEFT JOIN (SELECT t31.EmployeeID, t31.HRFileID, t32.HRFileName
FROM tblEmployeeFile t31
INNER JOIN dbo.tblHRFile t32 ON t31.HRFileID = t32.HRFileID) t3 ON t1.EmployeeID = t3.EmployeeID
LEFT JOIN dbo.tblCompany t4 ON t2.CompanyID = t4.CompanyID
LEFT JOIN dbo.tblDepartment t5 ON t2.DepartmentID = t5.DepartmentID
LEFT JOIN dbo.tblDeptUnit t6 ON t2.DeptUnitID = t6.DeptUnitID
LEFT JOIN dbo.tblJobTitle t7 ON t2.JobTitleID = t7.JobTitleID
LEFT JOIN dbo.tblTeam t8 ON t2.TeamID = t8.TeamID
WHERE 1 = 1 '

IF LEN(@EmployeeCode) > 0
SET @strSQL = @strSQL + ' AND t1.EmployeeCode LIKE @EmployeeCode '
IF LEN(@EmployeeName) > 0
SET @strSQL = @strSQL + ' AND t1.EmployeeName LIKE @EmployeeName '
IF LEN(@Gender)> 0
SET @strSQL = @strSQL + ' AND t1.Gender LIKE @Gender '
IF LEN(@CompanyName) > 0
SET @strSQL = @strSQL + ' AND t4.CompanyName LIKE @CompanyName '
IF LEN(@DepartmentName) > 0
SET @strSQL = @strSQL + ' AND t5.DepartmentName LIKE @DepartmentName '
IF LEN(@DeptUnitName) > 0
SET @strSQL = @strSQL + ' AND t6.DeptUnitName LIKE @DeptUnitname '
IF LEN(@JobTitleName) > 0
SET @strSQL = @strSQL + ' AND t7.JobTitleName LIKE @JobTitleName '
IF LEN(@TeamName) > 0
SET @strSQL = @strSQL + ' AND t8.TeamName LIKE @TeamName '
SET @strSQL = @strSQL + ' GROUP BY t1.EmployeeID
, t1.EmployeeCode
, t1.EmployeeName
, t1.Gender
, t4.CompanyName
, t5.DepartmentName
, t6.DeptUnitName
, t7.JobTitleName
, t8.TeamName '

SELECT @params = N'@FromDate SMALLDATETIME
, @ToDate SMALLDATETIME
, @EmployeeCode NVARCHAR(50)
, @EmployeeName NVARCHAR(255)
, @CompanyName NVARCHAR(255)
, @DepartmentName NVARCHAR(255)
, @DeptUnitName NVARCHAR(255)
, @JobTitleName NVARCHAR(255)
, @TeamName NVARCHAR(255)
, @Gender NVARCHAR(1)'

EXEC sp_executesql @strSQL, @params
, @FromDate = @FromDate
, @ToDate = @ToDate
, @EmployeeCode = @EmployeeCode
, @EmployeeName =@EmployeeName
, @CompanyName =@CompanyName
, @DepartmentName = @DepartmentName
, @DeptUnitName =@DeptUnitName
, @JobTitleName =@JobTitleName
, @TeamName =@TeamName
, @Gender = @Gender
END

No comments:

Post a Comment