我想在sql server2008中使用select命令时某些值为null [英] I want some values are null when am using select command in sql server2008
本文介绍了我想在sql server2008中使用select命令时某些值为null的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个ABC学院,那里有3个(X,Y,Z)课程,有计划花这么多钱吗
这是我的桌子
clgname programe specialid f1
ABC X 1 5000
ABC y 2 2000
ABC Z 3 4000
如何在此表上使用T-SQL select命令获得以下输出:
X programe y programe z programe
specialid
1 5000 Null or 0 Null or 0
2 Null or 0 2000空或0
3空或0空或0 4000
[e dit]添加了代码块 - OriginalGriff [/ edit]
解决方案
tbl as
(
select ' ABC' clgname,' X '程序, 1 特定ID, 5000 f1
union 全部
选择 ' ABC',' y', 2 , 2000
union all
选择 ' ABC',' Z', 3 , 4000
)
选择特定ID,
Isnull([x], 0 ) as [X programe],[y] as [Y programe], [z] as [Z programe]
from tbl
pivot(sum(sum) f1) for programe in ([x],[y],[z])) as pvt_tbl
快乐编码!
:)
< blockquote>你可以在sql server 2008中使用pivot Query来使用它
在同一个表中有500所大学,一些大学有所有的程序所以我的大学有1个程序,有些有2个程序如何一次只写一个选择命令
I have a ABC college in that 3 (X,Y,Z)programs are there programe wise spend some money like this
this is my table
clgname programe particularid f1
ABC X 1 5000
ABC y 2 2000
ABC Z 3 4000
How to use the T-SQL select command on this table to get the following output below:
X programe y programe z programe
particularid
1 5000 Null or 0 Null or 0
2 Null or 0 2000 Null or 0
3 Null or 0 Null or 0 4000
[edit]Code block added - OriginalGriff[/edit]
解决方案
with tbl as ( select 'ABC' clgname , 'X' programe , 1 particularid, 5000 f1 union all select 'ABC' , 'y' , 2 , 2000 union all select 'ABC', 'Z', 3 , 4000 ) select particularid, Isnull([x],0) as [X programe],[y] as [Y programe],[z] as [Z programe] from tbl pivot (sum(f1) for programe in ([x],[y],[z]))as pvt_tbl
Happy Coding!
:)
You can use this by using pivot Query in sql server 2008
in this same table am having 500 colleges,some colleges having all programs some colleges having 1 programe, some having 2 programs how to write only one select command at a time
这篇关于我想在sql server2008中使用select命令时某些值为null的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文