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.
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
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
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
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
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)
精彩评论