union select 的问题
--Create the table and insert values as example.
CREATE TABLE T (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int)
GO

INSERT INTO T VALUES (1,4,3,5,4,4)
INSERT INTO T VALUES (2,4,1,5,5,5)
INSERT INTO T VALUES (3,4,3,5,4,4)
INSERT INTO T VALUES (4,4,2,5,5,4)
INSERT INTO T VALUES (5,5,1,5,5,5)
GO
SELECT * FROM [T]
GO
SELECT vendorID, 'EMP1 ' AS emp,Emp1 FROM [T]
UNION SELECT
vendorID, 'EMP2 ' AS emp,Emp2 FROM [T]
ORDER BY emp

1EMP14
2EMP14
3EMP14
4EMP14
5EMP15
1EMP23
2EMP21
3EMP23
4EMP22
5EMP21


如果后面还有很多列 如 EMP7,8,9...
是否可以写出存储过程，不要一个个的把emp添加进来？


[解决办法]
可以用循环写成动态SQL
[解决办法]
--放入一存程中
Create ProceDure SP_TEST
As
Begin
Declare @S Varchar(8000)
Select @S = ' '
Select @S = @S + ' Union Select vendorID, ' ' ' + Name + ' ' ' As emp, ' + Name + ' FROM [T] '
From SysColumns Where ID = OBJECT_ID( 'T ') And Name Like 'Emp% ' Order By ColID
Select @S = Stuff(@S, 1, 7, ' ') + ' Order By emp '
EXEC(@S)
End
GO
EXEC SP_TEST
GO