小弟现有这样一张表：
ITEM PRO1 PRO2 PRO3 PRO4 PRO5
A 1 0 0 1 0
B 1 0 1 1 0
C 0 1 0 0 0
D 0 0 1 1 0
E 0 0 0 1 0
要求转成如下结果：
ITEM PRO
A PRO1
A PRO4
B PRO1
B PRO3
B PRO4
C PRO2
D PRO3
D PRO4
E PRO4
实际表中由于列数众多，有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 @[email protected]+'SELECT ITEM,PRO1 AS value,''PRO1'' AS PRO FROM TB'+CHAR(10)SELECT @[email protected]+'UNION ALL SELECT ITEM,'+NAME+','''+NAME+''' FROM TB '+CHAR(10) FROM SYSCOLUMNS WHERE ID=OBJECT_ID('TB') AND name NOT IN('ITEM','PRO1')SET @[email protected]+') A where value=1 order by item'exec (@SQL)结果为：ITEM PROA PRO1A PRO4B PRO4B PRO1B PRO3C PRO2D PRO3D PRO4E PRO4(9 行受影响)