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