Sr No display in Query

SELECT rank () over (order by <Column1>) as Sr_No,
Column1
FROM <TableName>

Use XML Path In SQL Server

SELECT  SUBSTRING(( SELECT DISTINCT ( ',' + Email )
                    FROM    TableName
                  FOR
                    XML PATH('')
                  ), 3, 1000) AS EmailList

Insert Data from TextFile to table

BULK INSERT dbo.User_MST
FROM 'c:\TestCSV.txt'
WITH
(
FIELDTERMINATOR =',',
ROWTERMINATOR='\n'
)
SELECT * FROM dbo.User_MST

Self Join Details

• What is a self join? Explain it with an example.
  • Self join is just like any other join, except that two instances of the same table will be joined in the query. Here is an example: Employees table which contains rows for normal employees as well as managers. So, to find out the managers of all the employees, you need a self join.
  • CREATE TABLE emp ( empid int, mgrid int, empname char(10) )
  • INSERT emp SELECT 1,2,’Vyas’ INSERT emp SELECT 2,3,’Mohan’ INSERT emp SELECT 3,NULL,’Shobha’ INSERT emp SELECT 4,2,’Shridhar’ INSERT emp SELECT 5,2,’Sourabh’
  • SELECT t1.empname [Employee], t2.empname [Manager] FROM emp t1, emp t2 WHERE t1.mgrid = t2.empid Here’s an advanced query using a LEFT OUTER JOIN that even returns the employees without managers (super bosses)
  • SELECT t1.empname [Employee], COALESCE(t2.empname, ‘No manager’) [Manager] FROM emp t1 LEFT OUTER JOIN emp t2 ON t1.mgrid = t2.empid