Get Row number from query

use Row_Number() function to get row numbers in rows

ex.

select Row_Number() over (order by fieldname) as rowid, * from [Tablename]

Export EXCEL File To SQL Server 2000 /2005 /2008

CREATE procedure [dbo].[InsertEXCELToSQLSERVER]
as

declare @Field1 nvarchar(50)
declare @Field2 nvarchar(50)

declare @CursorName cursor

set @CursorName = cursor
for
select * from openrowset('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=\\path\datafile.xls','select @Field1,@Field2 from [Sheet1$]')
open @CursorName
fetch next from @CursorName into @Field1, @Field2

WHILE @@FETCH_STATUS = 0
BEGIN
select @Field1, @Field2 ;

INSERT INTO Tablename (Field1,Field2) VALUES (@Field1, @Field2 )

fetch next from @UserCursor into @Field1,@Field2

END

CLOSE @CursorName
DEALLOCATE @CursorName

OutPut : following will execute and insert data in to table
exec InsertEXCELToSQLSERVER

Functions In Sqlserver

Table valued Functions

create function [dbo].[GetInformation](@ParameterId int)
returns table as
return (
select count(*) as total from tablename where condition=@ParameterId)

to view results
select * from GetInformation(10) where 10 is parent table's primary key Id

Scalar valued Functions

ALTER FUNCTION [dbo].[GenerateReceiptNumber](@FirstName varchar(50),@Year varchar(50))
RETURNS varchar(50)
AS
BEGIN
DECLARE @Result varchar(50)
Declare @FN varchar(50)
Declare @YR varchar(50)

set @FN = @FirstName
set @YR = @Year


SELECT @Result = upper(@FN + @YR)

RETURN @Result

END

to view results write

select dbo.GenerateReferenceNumber('MyName','2009')
OutOut -- > MyName2009

Use Of Pivot Table in Sqlserver 2005

Display Columns into Rows Using Pivot Table

here is the example that from Attendence table Want to get Yearly Attendence report By Student Wise

select StudentID,
StudentName,
isnull(January,0) January,
isnull(February,0) February,
isnull(March,0) March,
isnull(April,0) April,
isnull(May,0) May,
isnull(June,0) June,
isnull(July,0) July,
isnull(August,0) August,
isnull(September,0) September,
isnull(October,0) October,
isnull(November,0) November,
isnull(December,0) December
from
(
select StudentID, StudentName, DateName(m,AttendenceDate) [Month], Count(*) Total from AttendenceTable
Group by StudentID, DateName(m,AttendenceDate),StudentName
) as source
Pivot
(
sum(Total)
for [month] in (January,February,March,April,May,June,July,August,September, October, November, December)

) as result