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

Convert numeric for viewing easily

CREATE FUNCTION [dbo].[fnDisplayNumber](@dblNumber real, @intNumberType smallint = 0, @intNoOfDigitsAfterDecimal smallint=0)
--@NumberTime : 0 (American), 1(Vietnamese)
RETURNS nvarchar(30) AS
BEGIN
declare @i tinyint
declare @strLeft nvarchar(30)
declare @strRight nvarchar(30)
declare @strResult nvarchar(30)

if @intNoOfDigitsAfterDecimal<0 intnoofdigitsafterdecimal =" 0">10
begin
set @intNoOfDigitsAfterDecimal = 10
end

set @strResult = ltrim(str(@dblNumber, 30, @intNoOfDigitsAfterDecimal))

if charindex('.', @strResult) > 0
begin
set @strLeft = left(@strResult, charindex('.',@strResult)-1)
end
else
begin
set @strLeft = @strResult
end

set @strRight = right(@strResult, @intNoOfDigitsAfterDecimal)

set @i=1
set @strResult = ''
while @i<=len(@strLeft) begin set @strResult = @strResult + substring(@strLeft, @i, 1) if @i % 3 = len(@strLeft) % 3 AND @i <> len(@strLeft)
begin
if @intNumberType = 1
begin
Set @strResult = @strResult + '.'
end
else
begin
Set @strResult = @strResult + ','
end
end
set @i = @i + 1
end

if @intNoOfDigitsAfterDecimal>0
begin
if @intNumberType = 1
begin
Set @strResult = @strResult + ',' + @strRight
end
else
begin
Set @strResult = @strResult + '.' + @strRight
end
end

return @strResult
END

Calculate between two points on the earth

/*
Definitions:
South latitudes are negative, east longitudes are positive
Description:
This routine calculates the distance between two points (given the latitude/longitude of those points).
Passed to function:
@lat1, @lon1 = Latitude and Longitude of point 1 (in decimal degrees)
@lat2, @lon2 = Latitude and Longitude of point 2 (in decimal degrees)
@unit = the unit you desire for results
where: 'M' is statute miles
'K' is kilometers (default)
'N' is nautical miles
*/
CREATE FUNCTION dbo.fnGetDistance
(
@lat1 float,
@lon1 float,
@lat2 float,
@lon2 float,
@unit char(1)
)
RETURNS float
AS
BEGIN
DECLARE @theta FLOAT
DECLARE @dist FLOAT
DECLARE @pi FLOAT
SET @pi = 3.14159265358979323846
SET @theta = @lon1 - @lon2
SET @dist = sin(@lat1 * @pi/180) * sin(@lat2 * @pi/180)
+ cos(@lat1 * @pi/180) * cos(@lat2 * @pi/180) * cos(@theta * @pi/180)
SET @dist = acos(@dist)
SET @dist = @dist * 180/@pi
SET @dist = @dist * 60 * 1.1515
IF @unit = 'K'--Kilometer
SET @dist = @dist * 1.609344
ELSE IF @unit = 'N'--Nautical Miles
SET @dist = @dist * 0.8684

RETURN @dist
END

How to convert Vietnamese name to English name?

Sometimes, you want to convert Vietnamese name with Unicode font to English name without accent. How can you do that in T-SQL?
Here's my function to do that:

CREATE FUNCTION dbo.fnConvertToEnglishName(@VNString NVARCHAR(4000))
RETURNS NVARCHAR(4000)
AS
BEGIN
DECLARE @ENString NVARCHAR(4000)

DECLARE @I INT, @J INT
DECLARE @Array NVARCHAR(1000)

SET @Array = N'àảãáạằẳẵắặăầẩẫấậâa' +
N'èẻẽéẹềểễếệêeeeeeee' +
N'ìỉĩíịiiiiiiiiiiiii' +
N'òỏõóọồổỗốộôờởỡớợơo' +
N'ùủũúụừửữứựưuuuuuuu' +
N'ÈẺẼÉẸỀỂỄẾỆÊEEEEEEE' +
N'ÌỈĨÍỊIIIIIIIIIIIII' +
N'ÒỎÕÓỌỒỔỖỐỘÔỜỞỠỚỢƠO' +
N'ÀẢÃÁẠẰẲẴẮẶĂẦẨẪẤẬÂA' +
N'ÙỦŨÚỤỪỬỮỨỰƯUUUUUUU' +
N'ỳỷỹýỵyyyyyyyyyyyyy' +
N'ỲỶỸÝỴYYYYYYYYYYYYY' +
N'đddddddddddddddddd' +
N'ĐDDDDDDDDDDDDDDDDD'

SET @ENString = @VNString;

SET @I = 1;
SET @J = 1;
WHILE @I <= LEN(@Array) BEGIN IF (@I%18 = 0) BEGIN SET @J = @J + 1 END ELSE BEGIN IF CAST(SUBSTRING(@Array, @I, 1) AS VARBINARY) <> CAST(SUBSTRING(@Array, 18*@J, 1) AS VARBINARY)
SET @ENString = Replace(@ENString COLLATE Latin1_General_BIN,
SUBSTRING(@Array, @I, 1) ,
SUBSTRING(@Array, 18*@J, 1) );
END
SET @I = @I + 1;
END

RETURN @ENString;
END

How to get source of a stored procedure?

If the stored procedure is not encrypted, you can use this extended stored procedure:
sp_helptext 'StroreProcedureName'
You can find source of a view or a function similarly by using sp_helptext.

How to export a table to XML by T-SQL?

SELECT column list
FROM table list
WHERE filter criteria
FOR XML
RAW
| AUTO
| EXPLICIT [, XMLDATA] [, ELEMENTS] [, BINARY BASE64]

How to export a table to HTML by T-SQL?

EXEC sp_makewebtask @outputfile = 'C:\temp\cust_table.HTML', @query='SELECT CompanyName, CustomerID FROM Northwind..Customers ORDER BY CompanyName' , @lastupdated=0,@resultstitle=' '

How to find tables related to a column?

You are a DBA for a database, sometimes you need to know tables related to a column.
You can use this query to find that:

If you use Oracle database:

SELECT *

FROM all_tab_cols
WHERE table_name like ‘PartOfTable%’
AND column_name = ‘ColumnName’

If you use SQL Server 2005 database:
SELECT t1.*
FROM sys.columns t1 INNER JOIN
sys.objects t2 ON t1.object_id = t2.object_id
WHERE t1.name = 'ColumnName'