SELECT t1.Id,t.Value1,t.Value2,t1.Value3,t1.Value4 FROM

(SELECT ROW_NUMBER() OVER(ORDER BY Id)AS Row,Id,Value3,CONVERT(VARCHAR,Value4,101) AS Value4 FROM #tTable2) AS t1

LEFT JOIN

(SELECT ROW_NUMBER() OVER(ORDER BY Id)AS Row,Id,Value1,Value2 FROM #tTable1 )AS t

ON t.Row = t1.Row

DECLARE @Seq TABLE(Id INT,SID INT)

INSERT INTO @Seq VALUES(1,110),(2,104),(2,105),(3,110),(3,111),(3,112),(3,113),(3,114),(4,115),(4,116)

SELECT ID,Sid,

STUFF((SELECT ','+CAST(SId AS VARCHAR(10)) FROM @Seq AS SS

WHERE SS.SId > S.SId AND SS.Id = S.Id

ORDER BY SId DESC FOR XML...

CREATE TABLE Temp12(ProductName VARCHAR(20))

INSERT INTO Temp12 VALUES('123aaa'),('abcd'),('abc123'),('ab56def'),('xyz'),('9999'),('3946'),('9236'),('854')

USE DBTraining1

GO

IF OBJECT_ID('fn_GetAlphabets') Is Not Null

DROP FUNCTION dbo.fn_GetAlphabets

GO

CREATE FUNCTION fn_GetAlphabets(@input VARCHAR(20))

RETURNS VARCHAR(1000)

AS

BEGIN

WHILE PATINDEX('%[^a-z]%',@input) > 0

SET @input = STUFF(@input,PATINDEX('%[^a-z]%',@input),1,'')

RETURN @input

END

GO

SELECT * FROM Temp12

WHERE dbo.fn_GetAlphabets(Temp12.ProductName)...

See the syntax of dynamic sql .

DECLARE @cols AS NVARCHAR(MAX),

@query AS NVARCHAR(MAX)

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.ConditionId)

...

SELECT EI.EmplID,ER.Designation FROM #EmpIDs AS EI

INNER JOIN #EmpRoles AS ER

ON EI.EmplID = ER.EMPID

WHERE EI.EmplID IN (1,3)

UNION ALL

SELECT * FROM #LatestRoles

ORDER BY EmplID

DECLARE @Pvt TABLE (Field VARCHAR(20),FieldValue VARCHAR(10))

INSERT INTO @Pvt VALUES

('LastName','Davis'),('First Name','Ken'),('Salutation','Mr.')

SELECT [LastName],[First Name],[Salutation] FROM

(SELECT Field,FieldValue FROM @Pvt)e

PIVOT (MAX(FieldValue) FOR Field IN ([LastName],[First Name],[Salutation])) AS PVT

DECLARE @Pvt TABLE (Field VARCHAR(20),FieldValue VARCHAR(10))

INSERT INTO @Pvt VALUES

('LastName','Davis'),('First Name','Ken'),('Salutation','Mr.')

SELECT [LastName],[First Name],[Salutation] FROM

(SELECT Field,FieldValue FROM @Pvt)e

PIVOT (MAX(FieldValue) FOR Field IN ([LastName],[First Name],[Salutation])) AS PVT