create @tbl (colname varchar(8),colvalue int)
insert into @tbl select 'a',a from worda
.........
insert into @tbl select 'd',d from worda
select distinct colname from @tbl where colvalue=1
其余用游标把数值连接起来。
insert into @t
select 0,1,0,1 union all
select 1,1,0,0
select
(case a when 1 then 'a' when 0 then '' end) +
(case b when 1 then 'b' when 0 then '' end) +
(case c when 1 then 'c' when 0 then '' end) +
(case d when 1 then 'd' when 0 then '' end)
from @t
SELECT
a.colorder ColID,
a.name ColName
INTO #tmp
FROM syscolumns a
inner join sysobjects d
on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
where d.name='tab'
order by a.id,a.colorder
DECLARE @str VARCHAR(2000)
SET @str=''
SELECT @str=@str + 'ISNULL(RTRIM(NULLIF(1,' + ColName + ')),''' + ColName + ''')+' FROM #Tmp
SELECT @str=LEFT(@str,LEN(@str)-1)
EXEC ('SELECT REPLACE(' + @str + ',''1'','''') FROM tab')
DROP TABLE tab
DROP TABLE #tmp
create @tbl (colname varchar(8),colvalue int)
insert into @tbl select 'a',a from worda
.........
insert into @tbl select 'd',d from worda
select * from @tbl where colvalue=1