select rows as columns -- sql server programming

Ramesh Subramaniyan
1/28/2007 11:26:01 PM

[quoted text, click to view]

gjvdkamp NO[at]SPAM gmail.com
1/29/2007 1:05:01 AM

Select Col1, col2
where Col1 = 1
union all
Select Col1, col3
where Col1 = 1
union all
Select Col1, col4
where Col1 = 1
union all
Select Col1, col5
where Col1 = 1

Regards,
John

On 29 jan, 06:40, Ramesh Subramaniyan
[quoted text, click to view]

Dejan Sarka
1/29/2007 8:13:11 AM

SQL 2005 has UNPIVOT operator. Example from BOL:

--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

--
Dejan Sarka
http://www.solidqualitylearning.com/blogs/

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : select rows as columns