Converting Columns into rows with their respective data in sql server

Question

I have a scenario where I need to convert columns of table to rows eg - table - stocks:

ScripName       ScripCode       Price   
-----------------------------------------
20 MICRONS      533022      39  

I need to represent the table in the following format, but I just need this kind of representation for single row

ColName       ColValue
-----------------------------
ScripName      20 MICRONS
ScripCode      533022    
Price          39

so that I can directly bind the data to the datalist control.

Answer 1

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