求助：关于列转行
小弟现有这样一张表：
ITEMPRO1PRO2PRO3PRO4PRO5
A10010
B10110
C01000
D00110
E00010

要求转成如下结果：

ITEMPRO
APRO1
APRO4
BPRO1
BPRO3
BPRO4
CPRO2
DPRO3
DPRO4
EPRO4

实际表中由于列数众多，有60多列，因此不可能用UNION一个select item, pro .... UNION...这样的方法转
请教如何操作？先谢过了

[解决办法]
用UNPIVOT
http://technet.microsoft.com/zh-cn/library/ms177410.aspx#Mtps_DropDownFilterText
[解决办法]
参照例子
http://topic.csdn.net/u/20080614/17/22e73f33-f071-46dc-b9bf-321204b1656f.html
[解决办法]

SQL code

create table tb(ITEM VARCHAR(2),PRO1 BIT,PRO2 BIT ,PRO3 BIT ,PRO4 BIT ,PRO5 BIT)INSERT INTO TB (ITEM,PRO1,PRO2,PRO3,PRO4,PRO5)SELECT 'A',1,0,0,1,0 UNION ALLSELECT 'B',1,0,1,1,0 UNION ALLSELECT 'C',0,1,0,0,0 UNION ALLSELECT 'D',0,0,1,1,0 UNION ALLSELECT 'E',0,0,0,1,0GOSELECT * FROM tbGODECLARE @SQL VARCHAR(MAX)SET @SQL='SELECT ITEM,PRO FROM ('+CHAR(10)SET @SQL=@SQL+'SELECT ITEM,PRO1 AS value,''PRO1'' AS PRO FROM TB'+CHAR(10)SELECT @SQL=@SQL+'UNION ALL SELECT ITEM,'+NAME+','''+NAME+''' FROM TB '+CHAR(10) FROM SYSCOLUMNS WHERE ID=OBJECT_ID('TB') AND name NOT IN('ITEM','PRO1')SET @SQL=@SQL+') A where value=1 order by item'exec  (@SQL)结果为：ITEM    PROA    PRO1A    PRO4B    PRO4B    PRO1B    PRO3C    PRO2D    PRO3D    PRO4E    PRO4(9 行受影响)