Converting Columns into rows with their respective data in sql server

Sound like you want to UNPIVOT

Sample from books online:

--Create the table and insert values as portrayed in the previous example.
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
    Emp3 int, Emp4 int, Emp5 int);
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4);
INSERT INTO pvt VALUES (2,4,1,5,5,5);
INSERT INTO pvt VALUES (3,4,3,5,4,4);
INSERT INTO pvt VALUES (4,4,2,5,5,4);
INSERT INTO pvt VALUES (5,5,1,5,5,5);
GO
--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM 
   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
   FROM pvt) p
UNPIVOT
   (Orders FOR Employee IN 
      (Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;
GO

Returns:

VendorID   Employee   Orders
---------- ---------- ------
1          Emp1       4
1          Emp2       3
1          Emp3       5
1          Emp4       4
1          Emp5       4
2          Emp1       4
2          Emp2       1
2          Emp3       5
2          Emp4       5
2          Emp5       5

see also: Unpivot SQL thingie and the unpivot tag


declare @T table (ScripName varchar(50), ScripCode varchar(50), Price int)
insert into @T values ('20 MICRONS', '533022', 39)

select 
  'ScripName' as ColName,
  ScripName as ColValue
from @T
union all
select 
  'ScripCode' as ColName,
  ScripCode as ColValue
from @T
union all
select 
  'Price' as ColName,
  cast(Price as varchar(50)) as ColValue
from @T

select 'ScriptName', scriptName from table
union all
select 'ScriptCode', scriptCode from table
union all
select 'Price', price from table

DECLARE @TABLE TABLE 
  (RowNo INT,ScripName  VARCHAR(10),ScripCode  VARCHAR(10)
  ,Price  VARCHAR(10))      
INSERT INTO @TABLE VALUES
  (1,'20 MICRONS ','533022','39')
SELECT ColumnName,ColumnValue from @Table
 Unpivot(ColumnValue For ColumnName IN (ScripName,ScripCode,Price)) AS H