在线等:求一sql语句

[liaoxiaohua1981]

表worda
有四个字段 a b c d
有条记录值为 0 1 0 1

我要统计出这条记录中值为1的字段名
即呈现结果为: b d
该怎么写sql

[liaoxiaohua1981]

没人回答吗?自己先顶上

[xiaoman_xiaoman]

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

[云中客]

楼上好像没有理解楼主的意思吧,是要显示字段名,而不是把这字段名当做值来显示

[云中客]

这个问题有难度,关注

[fcuandy]

CREATE TABLE tab(a int,b int,c int,d int)
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.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

[fcuandy]

我的写法是针对数据表的字段名和字段数不定的情况下,也就是说比较通用.

如果有多条记录
有四个字段 a b c d
有条记录值为 0 1 0 1
有条记录值为 1 1 0 1
楼主所需的结果是 abd

我上面的语句稍作修改就可以了.
RTRIM(NULLIF(1,' + ColName + '这里稍改改改成 对ColName的sum并与0比较NULLIF即可.

[dssw]

declare @t table (a int, b int , c int ,d int )

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

[losedxyz]

mark

[xiaoman_xiaoman]

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
其余用游标把数值连接起来。