CTE

CTE (Common Table Expression):


WITH name (Alias name of the retrieve result set fields)
AS
(
//Write the sql query here
)
SELECT * FROM name


CTE 1: Simple CTE

WITH ProductCTE
AS
(
SELECT ProductID AS [ID],ProductName AS [Name],CategoryID AS [CID],UnitPrice AS [Price]
FROM Products
)
SELECT * FROM ProductCTE

Update group of records

SqlDataAdapter daAuthors = new SqlDataAdapter("Select Id,Name From ttt", DA.Data.SqlConnectionString.Conn);
DataSet ds = new DataSet("Pubs");
daAuthors.Fill(ds, "ttt");
ds.Tables[0].TableName = "ttt";
DataRow dr=ds.Tables[0].NewRow();
dr["Name"]="Arun";
ds.Tables[0].Rows.Add(dr);

SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(daAuthors);
daAuthors.InsertCommand = cmdBuilder.GetInsertCommand(true);
daAuthors.UpdateCommand = cmdBuilder.GetUpdateCommand(true);
daAuthors.DeleteCommand = cmdBuilder.GetDeleteCommand(true);
daAuthors.Update(ds, "ttt");

Mobile call when click a link

brokerPro.append(CreateBrokerElements('Email: ', '' + result.Items[i].Email + ''));

Pivot table eg: (SQL)

Create Table #TempResult(Sno int,AllocationMonthYear varchar(100),Efforts numeric(18,2),
Location varchar(100),Release nvarchar(100), Category nvarchar(100),
Name nvarchar(50),Mon Int, Ye int)
Insert Into #TempResult
SELECT
Sno
,cast(DateName(mm,DATEADD(mm,ActualAllocationInMonthsDtl.Month,-1)) as varchar) +' '+
cast(ActualAllocationInMonthsDtl.Year as varchar) AllocationMonthYear
,Sum(ActualAllocationInMonthsDtl.Efforts)Efforts
,ActualAllocationInMonthsDtl.Location
,PlannedAllocationHdr.Release
,PlannedAllocationHdr.Category
,Designation.Name AS Designation
,ActualAllocationInMonthsDtl.Month
,ActualAllocationInMonthsDtl.Year
FROM
ActualAllocationInMonthsDtl
INNER JOIN
ActualAllocationInMonthsHdr ON ActualAllocationInMonthsDtl.AllocationHdrUid = ActualAllocationInMonthsHdr.Uid
INNER JOIN
ActualVsPlanned ON ActualAllocationInMonthsHdr.Uid = ActualVsPlanned.ActualAllocationId
INNER JOIN
Designation ON ActualVsPlanned.DesignationUid = Designation.Uid
INNER JOIN
PlannedAllocationHdr ON ActualVsPlanned.PlannedAllocationId = PlannedAllocationHdr.Uid WHERE ActualAllocationInMonthsHdr.ProjectId=@projectUid
GROUP By
Sno,ActualAllocationInMonthsDtl.Month,
ActualAllocationInMonthsDtl.Year,
ActualAllocationInMonthsDtl.Location,
PlannedAllocationHdr.Release,
PlannedAllocationHdr.Category,
Designation.Name
Order By ActualAllocationInMonthsDtl.Year,ActualAllocationInMonthsDtl.Month

Declare @TblMonthYear Table(Mon int,Yea int)
Insert Into @TblMonthYear Select Distinct Mon,Ye From #TempResult Order By Ye,Mon

DECLARE @PivotColumnHeaders VARCHAR(MAX)

SELECT @PivotColumnHeaders =
COALESCE(
@PivotColumnHeaders + ',[' + cast(DateName(mm,DATEADD(mm,Mon,-1)) as varchar) +' '+ cast(Yea as varchar) + ']',
'[' + cast(DateName(mm,DATEADD(mm,Mon,-1)) as varchar) +' '+ cast(Yea as varchar) + ']'
)
FROM (Select Mon,Yea From @TblMonthYear) as test

DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N'
SELECT *
FROM
(SELECT Sno,AllocationMonthYear,Efforts,Release,Category,Location,Name AS Designation
FROM #TempResult) AS DataTable
PIVOT
(
Max(Efforts)
FOR AllocationMonthYear IN ('+ @PivotColumnHeaders +')
) AS PivotTable;
'
EXECUTE(@PivotTableSQL)

Reset Identity Field in SQL Table

DBCC CHECKIDENT (TableName, RESEED, 0)

Reset Identity Field in SQL Table

DBCC CHECKIDENT (TableName, RESEED, 0)