Columns into rows? how to? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Columns into rows? how to?

Author

Topic

madlo
Starting Member

41 Posts

Posted - 2009-03-13 : 02:26:17

Hi,
I am writing a stored proc that I need to use to create a multiline file that need to return rows of columns off of a sub select statement.
for example
Select ?????
FROM
(
--Multi table join
select Cola,Colb,Colc from
tab1,tabx
)MyTable

so my data from the subquery simplified is
a1,b1,c1
a2,b2,c2
...
and must be returned as
1,a1
2,b1
3,c1
4,a2
5,b2
6,c2
.
.
.

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-13 : 02:27:05

use unpivot , it will be easier to u

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-13 : 02:27:30

check this once,

CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int)

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)

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources