Converting Columns into rows with their respective data in sql server

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

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

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

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