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

1 EMP1 4
2 EMP1 4
3 EMP1 4
4 EMP1 4
5 EMP1 5
1 EMP2 3
2 EMP2 1
3 EMP2 3
4 EMP2 2
5 EMP2 1


如果后面还有很多列   如   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