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
insert into @tbl select 'a',a from worda
.........
insert into @tbl select 'd',d from worda
select * from @tbl where colvalue=1
INSERT tab
SELECT 0,1,1,0
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.colorderDECLARE @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
有四个字段 a b c d
有条记录值为 0 1 0 1
有条记录值为 1 1 0 1
楼主所需的结果是 abd我上面的语句稍作修改就可以了.
RTRIM(NULLIF(1,' + ColName + '这里稍改改改成 对ColName的sum并与0比较NULLIF即可.
select 0,1,0,1 union all
select 1,1,0,0select
(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
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
其余用游标把数值连接起来。