根据条件求列数

[nm3000]

求一个表的列数我倒是会，但是怎样求满足条件的列数呢？
比如说一个表有d1,d2,d3,d4,d5,d6,d7,d8,d9这九个列，怎样求这九列中列的值等于“S”的列数

[chenjunjarysky]

select count(列名) from table where 列名='s'

[nm3000]

列名是不固定的有可能是d1也可能是d2或者是……，怎么能用count（列名）呢

[xyxfly]

declare @col varchar(30)
set @col='....'
exec('select count('+@col+') from table where 列名='''+@col+'''')

[Well]

那循环访问了。。

[Well]

--根据值求此值在某个表的那个字段中存在
--建立测试环境
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TestA]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TestA]
GO

CREATE TABLE [dbo].[TestA] (
,[fs] [varchar] (50) ,
,[nl] [varchar] (100),
,[gz] [int] NULL
) ON [PRIMARY]
GO
--添加测试数据
insert into TestA
select '分配方式','20/40/60',1000 union all
select '分配方式','60/70/40',2000 union all
select '其它','30',800
--测试
declare @vSQLString varchar(8000)
,@vValue varchar(100)
select @vSQLString='declare @vColumnString varchar(2000)'+char(13)+'set @vColumnString='''''+char(13)
,@vValue='2'

select @vSQLString=@vSQLString+'if exists(select top 1 0 from testA where '+name+' like ''%'+@vValue+'%'') '
+char(13)+'set @vColumnString=@vColumnString+'''+name+';'''+char(13)
from syscolumns where id=object_id('testA')
set @vSQLString=@vSQLString+'select @vColumnString HaveValueColumnName'
--print @vSQLString
exec(@vSQLString)
--显示结果
/*
HaveValueColumnName
gz;nl;
*/
--删除测试环境
drop table testA

[zhenmeiyisi]

SET NOCOUNT ON
declare @id int,@name varchar(100),@r int
set @id=0
declare cur CURSOR for select name from syscolumns where object_id('test')=id
open cur
fetch next from cur into @name
while @@fetch_status = 0
begin
if exists(select count(1) from test where @name='1')
begin
set @id=@id+1
end
fetch next from cur into @name
end
print @id ---------列数
close cur
deallocate cur
SET NOCOUNT Off

[njz168]

借用以上的测试数据：
CREATE TABLE [dbo].[TestA] (
[fs] [varchar] (50)
,[nl] [varchar] (100)
,[gz] [int] NULL
) ON [PRIMARY]
GO
--添加测试数据
insert into TestA
select '分配方式','20/40/60',1000 union all
select '分配方式','60/70/40',2000 union all
select '60/70/40','分配方式',2000 union all
select '其它','30',800

--测试：
exec proc_test 'TestA','分配方式'

--结果
（所影响的行数为 0 行）


（所影响的行数为 2 行）

fs,nl

[nm3000]

问题解决
谢谢zhenmeiyisi(。。。) 给我一个很好的思路

[njz168]

列转行：
create proc proc_test(@tbname varchar(100), @value varchar(128))
as
begin
declare @sql varchar(4000)
declare @cols varchar(500)
select @sql = '',@cols = ''
select @sql = @sql + case @sql when '' then 'select ' else ' union all select distinct ''' +name+''' as colname,'+name+' as value from ' + @tbname +' where '+name+ '= '''+@value+''''+ char(10)
from syscolumns where id = object_id(@tbname)
insert into # exec(@sql)
select @cols = @cols +case @cols when '' then '' else ',' end + colname
from #
print @cols
drop table #
end

[njz168]

create proc proc_test(@tbname varchar(100), @value varchar(128))
as
begin
declare @sql varchar(4000)
declare @cols varchar(500)
select @sql = '',@cols = ''
select @sql = @sql + case @sql when '' then 'select distinct ''' else ' union all select distinct ''' end +name+''' as colname,cast('+name+' as varchar(128) ) as value from ' + @tbname +' where cast('+name+ ' as varchar(128)) = '''+@value+''''+ char(10)
from syscolumns where id = object_id(@tbname)

select cast('' as varchar(128)) colname,cast('' as varchar(128)) value into #
from sysobjects
where 1=2
-- print @sql
insert into # exec(@sql)
select @cols = @cols +case @cols when '' then '' else ',' end + colname from #

print @cols

drop table #
end