select column data as row by using sql server

Hi,
In this article we will discuss about how to select or display column data as row by using sql server.

1) Create table

CREATE TABLE YourTable
(RowID int primary key not null identity(1,1)
,emp_id int null
,emp_name varchar(100)
,Last_name varchar(100)
,salary money
,StatusValue char(1)
,ranks int
)

2) Insert Records in to table

INSERT INTO YourTable (emp_id,emp_name,Last_name,salary,StatusValue,ranks) VALUES (1,'sandip','Gaikwad',1234.56,'A',12)
INSERT INTO YourTable (emp_id,emp_name,Last_name,salary,StatusValue,ranks) VALUES (2,'pranav','Gaikwad',14034.56,'I',102)

3) Check origanal data

3) Check below query to select column data as row.

DECLARE @YourTableName varchar(1000)
DECLARE @YourTableWhere varchar(1000)
DECLARE @YourQuery varchar(max)
SET @YourTableName='YourTable'
set @YourTableWhere='y.RowID=1'
SELECT
@YourQuery = STUFF(
(SELECT +
' UNION '
+ 'SELECT '''+COLUMN_NAME+''' as Col_name, CONVERT(varchar(max) ,'+COLUMN_NAME+') as column_data FROM '+@YourTableName+' y'+ISNULL(' WHERE '+@YourTableWhere,'')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = @YourTableName
FOR XML PATH('')
), 1, 7, ''
)

4) Check output ::

EXEC (@YourQuery)