Hi,

I would like to know on how to pass a column name into a particular variable.

Example

Table_name       Column_name

Tablenm             Name

Tablenm             Age

Tablenm             Phone

The output will be:

ColName        Value

Name             Alexander Grahambell

Age                 32

Phone             123456

Name             Sanosuke Sagara

Age                 35

Phone             246790

Thanks for the help :)

;with cte as (select cast(Name as nvarchar(50)) as Name, cast(age as nvarchar(50)) as Age, cast(phone as nvarchar(50)) as Phone from PersonInfo)

select * from cte UNPIVOT (Value for ColumnName IN ([Name], [Age],[Phone])) unpvt

--------------

The trick here is to make all columns to be of the same type. If your name and phone are varchar and Age is int, you can cast to varchar and pick the longest length among the columns.

Try this 

 ;WITH T1 (COLUMNNAMES)AS(  SELECT stuff((select ',' + quotename(COLUMN_NAME) AS COLUMNNAMES
          FROM   (select distinct COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'YourTableName') X
          ORDER BY COLUMN_NAME
          For XML Path(''),type).value('.', 'VARCHAR(max)'),1,1,''))
 SELECT COLUMNNAMES FROM T1

I would like to know on how to pass a column name into a particular variable.

This is not how to write SQL. You are supposed to know what the schema looks like. Columns (attributes) are not variables (scalar values).  A bad SQL programmer will use dynamic SQL to make up for his poor design, but try to be better than that. 

You can do this in a couple of ways. One, is :

SET @sql = 'SELECT ' + @columnName + ' FROM yourTable'
sp_executesql @sql

If you opt for that method, be very certain to santise your input. Even if you know your application will only give 'real' column names, what if some-one finds a crack in your security and is able to execute the SP directly? Then they can execute just about anything they like. With dynamic SQL, always,always, validate the parameters.

Alternatively, you can write a CASE statement...

SELECT
  CASE @columnName
    WHEN 'Col1' THEN Col1
    WHEN 'Col2' THEN Col2
                ELSE NULL
  END as selectedColumn
FROM
  yourTable

This is a bit more long winded, but a whole lot more secure.

Another way is to:

DECLARE @sql nvarchar(max) = 'SELECT ' + @columnname + ' FROM Table_1';
exec sp_executesql @sql, N''

Note that you can pass the column name but you cannot use it in a sql statemnt like

Select @Columnname From Table

Either use dynamic SQL (dangerous) or a huge case expression (slow)

For more information see this answer on dynamic sql.

Dynamic SQL Pros and Cons