表study 字段 id,a,b,c,d,e
有几条记录 1,60,70,80,null,90
2,null,null,54,80,90
写条查询语句 当id=1时 输出结果 1,60,70,80,90 null值的那列不要 只输出有值的那些列!查询语句怎么写啊???
有几条记录 1,60,70,80,null,90
2,null,null,54,80,90
写条查询语句 当id=1时 输出结果 1,60,70,80,90 null值的那列不要 只输出有值的那些列!查询语句怎么写啊???
declare @study table(
id int,
a int null,
b int null,
c int null,
d int null,
e int null
)
insert into @study select 1,60,70,80,null,90
union all select 2,null,null,54,80,90select
case when (t.a + t.b + t.c + t.d + t.e) <> '' then
convert(char, t.id) + (t.a + t.b + t.c + t.d + t.e)
else
t.id
end
from
(select
convert(char, id) as id,
(case when a is not null then ',' + convert(char, a) else '' end ) as a,
(case when b is not null then ',' + convert(char, b) else '' end ) as b,
(case when c is not null then ',' + convert(char, c) else '' end ) as c,
(case when d is not null then ',' + convert(char, d) else '' end ) as d,
(case when e is not null then ',' + convert(char, e) else '' end ) as e
from @study where id = 2) as t
当为null时等于0
select ID,
col1=isnull(col1,0),
col2=isnull(col2,0)
from
t
where id=1
declare @sql varchar(1000)
declare @tsql nvarchar(1000)
declare @colname nvarchar(256)
declare @temp varchar(100)set @id = 1
set @sql = 'select '
set @tsql = N''declare cur cursor for
select convert(nvarchar(256),name)
from dbo.syscolumns
where object_name(id) = '{<tablename>}
open cur
fetch next from cur into @colname
while @@fetch_status = 0
begin
set @tsql = N'select @temp = convert(varchar(100),' + @colname + ') from {<tablename>}
exec sp_executesql @tsql,N'@temp varchar(100) output',@temp output
if @temp is not null
begin
select @sql = @sql + @colname + ','
end
fetch next from cur into @colname
end
close cur
deallocate curset @sql = substring(@sql,1,len(@sql) - 1 ) + ' from {<tablename>}exec(@sql)
go--{<tablename>}为你的表名
declare @sql varchar(1000)
declare @tsql nvarchar(1000)
declare @colname nvarchar(256)
declare @temp varchar(100)set @id = 1
set @sql = 'select '
set @tsql = N''declare cur cursor for
select convert(nvarchar(256),name)
from dbo.syscolumns
where object_name(id) = '{<tablename>}'
open cur
fetch next from cur into @colname
while @@fetch_status = 0
begin
set @tsql = N'select @temp = convert(varchar(100),' + @colname + ') from {<tablename>}'
exec sp_executesql @tsql,N'@temp varchar(100) output',@temp output
if @temp is not null
begin
select @sql = @sql + @colname + ','
end
fetch next from cur into @colname
end
close cur
deallocate curset @sql = substring(@sql,1,len(@sql) - 1 ) + ' from {<tablename>}'exec(@sql)
go
declare @sql varchar(1000)
declare @tsql nvarchar(1000)
declare @colname nvarchar(256)
declare @temp varchar(100)set @id = 1
set @sql = 'select '
set @tsql = N''declare cur cursor for
select convert(nvarchar(256),name)
from dbo.syscolumns
where object_name(id) = '{<tablename>}'
open cur
fetch next from cur into @colname
while @@fetch_status = 0
begin
set @tsql = N'select @temp = convert(varchar(100),' + @colname + ') from {<tablename>} where id = 1'
exec sp_executesql @tsql,N'@temp varchar(100) output',@temp output
if @temp is not null
begin
select @sql = @sql + @colname + ','
end
fetch next from cur into @colname
end
close cur
deallocate curset @sql = substring(@sql,1,len(@sql) - 1 ) + ' from {<tablename>}'exec(@sql)
go
-------------------null值的那列不要 只输出有值的那些列!
楼主应该是要筛选列,不是输出一列为非NULL列的字符组合~
insert #T select 1,60,70,80,null,90
insert #T select 2,null,null,54,80,90declare @sql varchar(100),@id intset @id=2select @sql=
'select id'+
isnull(',a='+ltrim(a),'')+
isnull(',b='+ltrim(b),'')+
isnull(',c='+ltrim(c),'')+
isnull(',d='+ltrim(d),'')+
isnull(',e='+ltrim(e),'')+
' from #T where id='+ltrim(@id)
from #T where id=@idexec (@sql)drop table #T
要是要求返回多条记录,并且每个存在非null值的列都单独为列,那确实 impossible如果不要求每个列都单独为列,那么处理方法还是有多种的,随手敲一种.
create table study(
id int,
a int null,
b int null,
c int null,
d int null,
e int null
)
insert study select 1,60,70,80,null,90
union all select 2,null,null,54,80,90
union all select 3,null,342,null,10,22declare @ids varchar(100)
select @ids='1,3'declare @s varchar(4000)
select @s=isnull(@s,'') + ' union all select id='+rtrim(id) +',show='''+isnull(rtrim(a)+' ','')+isnull(rtrim(b)+' ','')+isnull(rtrim(c)+' ','')+isnull(rtrim(d)+' ','')+isnull(rtrim(e)+' ','')+'''' from study
select @s=stuff(@s,1,10,'')exec('select * from ('+@s+')x where charindex('',''+rtrim(id)+'','' , '',' + @ids + ','')>0')drop table study
replace(
+ isnull(convert(varchar(20),id),'null')
+ ','
+ isnull(convert(varchar(20),a),'null')
+ ','
+ isnull(b,'null')
+ ','
+ isnull(convert(varchar(20),c),'null')
+ ','
+ isnull(d,'null'),',null','')
from table
where id = 1
--只要是非字符型,就直接ISNULL判断,如果是INT,就先转换在ISNULL,用得着那么麻烦么
如果我的表不只有两条记录,有几十条记录!那用此方法,起不是要很多次insert!
我是不是数据库设计错了!
需求是这样的:一个班50名学生,每个学生选修不同的课程,输入学号,输出成绩.
因为是选修课,所以选的课程不一样.
我是直接建一张表study,然后用课程做字段,这样没有选到的课程,就为NULL值.
是不是应该把数据库改成每科成绩建一个表SQL codecreate table #T (id int,a int,b int,c int,d int,e int)
insert #T select 1,60,70,80,null,90
insert #T select 2,null,null,54,80,90declare @sql varchar(100),@id intset @id=2select @sql=
'select id'+
isnull(',a='+ltrim(a),'')+
isnull(',b='+ltrim(b),'')+
isnull(',c='+ltrim(c),'')+
isnull(',d='+ltrim(d),'')+
isnull(',e='+ltrim(e),'')+
' from #T where id='+ltrim(@id)
from #T where id=@idexec (@sql)drop table #T
你原来的(设id对应于学生学号)id subject1 subject2 subject3
1 99 88 77
2 90 98 78改后的成为
nid id subject score
1 1 subject1 99
2 1 subject2 88
3 1 subject3 77
4 2 subject1 09
...
这样有比较好的可处理性和扩充性.
只是在查询结果程现时需要使用行转列.