May time we need column's data into a single row from a database table. This is easy to do if we are doing this at application level. However this could also be done in a sql query as well.

For example, suppose we have a table for "Employees":

SELECT * FROM Employees

EmpId      EmployeeName
-----------------------
1001         Susan
1002         Waqas
1003         Amjad
1004         Naveed
1005         David

Now I want to show all employee names in a single row separated by comma (,). One commonly used approach is to fetch all rows and concatenate data into out application

while (dr.Read())
{
  EmployeeNames += dr["EmployeeName"] + ",";
}

We can do the same thing in a Sql Server Query also

DECLARE @EmployeeName VARCHAR(8000) 
SELECT @EmployeeName = COALESCE(@EmployeeName + ', ', '') + EmployeeName FROM Employees
SELECT EmployeeName = @EmployeeName

EmployeeName
------------
Susan, Waqas, Amjad, Naveed, David

The COALESCE function is used to ensure that there is no comma (,) after the last EmployeeName.

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '<TableNameGoesHere>'

Tags: SQL

An INNER  JOIN combines the records from two tables using comparison operators in a condition. Columns are returned only where the joined rows match the condition. 

Here the quick review of the syntax of the INNER JOIN: 

SELECT somecolumns
FROM table1
INNER  JOIN
table2
ON somecondition

If the condition (somecondition in above syntax) tests the equality then INNER JOIN becomes EQUIJOIN. EQUIJOIN tests for equality.

Similarly, if the same condition testes in-equality using <> symbol, then INNER JOIN becomes NON-EQUIJOIN.

Last but not the least, is the NATURAL JOIN. Natural Joins only work if the columns you are joining by has the same name in both tables. So NATURAL JOIN, bascially identifies joining column names automatically and you don't have to specify condition.