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 ALL
SELECT 'B',1,0,1,1,0 UNION ALL
SELECT 'C',0,1,0,0,0 UNION ALL
SELECT 'D',0,0,1,1,0 UNION ALL
SELECT 'E',0,0,0,1,0
GO
SELECT * FROM tb
GO
DECLARE @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    PRO
A    PRO1
A    PRO4
B    PRO4
B    PRO1
B    PRO3
C    PRO2
D    PRO3
D    PRO4
E    PRO4

(9 行受影响)