[sql-server] Converting Columns into rows with their respective data in sql server

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.

As an alternative:

Using CROSS APPLY and VALUES performs this operation quite simply and efficiently with just a single pass of the table (unlike union queries that do one pass for every column)

SELECT
    ca.ColName, ca.ColValue
FROM YOurTable
CROSS APPLY (
      Values
         ('ScripName' , ScripName),
         ('ScripCode' , ScripCode),
         ('Price'     , cast(Price as varchar(50)) )
  ) as CA (ColName, ColValue)

Personally I find this syntax easier than using unpivot.

NB: You must take care that all source columns are converted into compatible types for the single value column

i solved the query this way

SELECT
    ca.ID, ca.[Name]
FROM [Emp2]
CROSS APPLY (
      Values
         ('ID' , cast(ID as varchar)),
         ('[Name]' , Name)

  ) as CA (ID, Name)

output look like

  ID     Name
------ --------------------------------------------------
ID     1
[Name] Joy
ID     2
[Name] jean
ID     4
[Name] paul

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

CREATE TABLE #ORIGINAL
(
    COUNTRY VARCHAR(50),
    MALE_CRICKETER VARCHAR(50),
    FEMALE_CRICKETER VARCHAR(50),
    MALE_STAR VARCHAR(50),
    FEMALE_STAR VARCHAR(50),
)

select * from #ORIGINAL 
SELECT COUNTRY, ca.GENDER, ca.STAR, ca.CRICKETR
FROM #ORIGINAL
CROSS APPLY (
      Values
         ('M', MALE_CRICKETER, MALE_STAR),
         ('F', FEMALE_CRICKETER, FEMALE_STAR)

  ) as CA (GENDER, CRICKETR, STAR)

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

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