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