下面是以前,paoluo(一天到晚游泳的鱼)给我的一个答案,很好用,再次感谢 Create Table TEST
(Type Char(1),
State Char(1))
Insert TEST
Select 'A','a' Union All
Select 'A','b' Union All
Select 'A','b' Union All
Select 'B','a' Union All
Select 'B','a' Union All
Select 'B','b' Union All
Select 'C','a' Union All
Select 'C','b'
GO
--State不固定的情況下的
Declare @S Varchar(8000)
Select @S=''
Select @S=@S+',SUM(Case State When '''+State+'''Then 1 Else 0 End) As '+State
From TEST Group By State Order By State
Select @S='Select Type'+@S+' From TEST Group By Type'
EXEC(@S)
GO
Drop Table TEST
GO
--Resilt
/*
Type a b
A 1 2
B 2 1
C 1 1
*/现在的问题是,现在我把State 改为datetime类型,就不知道该怎么对下面这句话进行赋值了
Select @S=@S+',SUM(Case State When '''+State+'''Then 1 Else 0 End) As '+State
From TEST Group By State Order By State总是提示:字符转datetime类型时出错改为这样后也不行,晕了
Select @S=@S+',SUM(Case State When cast('''+State+''' as datetime) Then 1 Else 0 End) As '+State
From TEST Group By State Order By State
(Type Char(1),
State Char(1))
Insert TEST
Select 'A','a' Union All
Select 'A','b' Union All
Select 'A','b' Union All
Select 'B','a' Union All
Select 'B','a' Union All
Select 'B','b' Union All
Select 'C','a' Union All
Select 'C','b'
GO
--State不固定的情況下的
Declare @S Varchar(8000)
Select @S=''
Select @S=@S+',SUM(Case State When '''+State+'''Then 1 Else 0 End) As '+State
From TEST Group By State Order By State
Select @S='Select Type'+@S+' From TEST Group By Type'
EXEC(@S)
GO
Drop Table TEST
GO
--Resilt
/*
Type a b
A 1 2
B 2 1
C 1 1
*/现在的问题是,现在我把State 改为datetime类型,就不知道该怎么对下面这句话进行赋值了
Select @S=@S+',SUM(Case State When '''+State+'''Then 1 Else 0 End) As '+State
From TEST Group By State Order By State总是提示:字符转datetime类型时出错改为这样后也不行,晕了
Select @S=@S+',SUM(Case State When cast('''+State+''' as datetime) Then 1 Else 0 End) As '+State
From TEST Group By State Order By State
解决方案 »
- 一个SQL 判断 空值的问题。解决不了。急求大大解答
- |zyciis| 如何取出一条记录的ID号,同时更新他的操作状态Sel 而且在并发的时候不会出错重复读取 谢谢 第二贴
- 问一句比较麻烦的SQL语句
- 谁有办法,能将SQL7.0与SQL2000连起来?
- 新手请问:如何解决这个问题?case /where
- 高分求sqlserver写excel
- 求个SQL
- backup log 数据库 WITH NO_LOG 命令是什么意思,都执行了什么操作!
- 如何让查询结果不按照ID自动排序
- 这个关系模式是不满足2NF?
- 重装了win2003系统和sqlserver2000数据库,不知道为什么原来的数据库里时间字段变成 2006-2-21 下午 01:49:06 这种格式了,怎么回事?
- mssqlserver导出文件出错
Create Table TEST
(Type Char(1),
State datetime --此处改为datetime类型
Insert TEST
Select 'A','2006-6-6' Union All
Select 'A','2006-6-7' Union All
Select 'A','2006-6-7' Union All
Select 'B','2006-6-6' Union All
Select 'B','2006-6-6' Union All
Select 'B','2006-6-7' Union All
Select 'C','2006-6-6' Union All
Select 'C','2006-6-7'
GO
Select @S=@S+',count(*) As '+State
From TEST
Group By State
Order By State
(Type Char(1),
State datetime
)
Insert TEST
Select 'A','2006-08-10' Union All
Select 'A','2006-08-14' Union All
Select 'A','2006-08-13' Union All
Select 'B','2006-08-20' Union All
Select 'B','2006-08-17' Union All
Select 'B','2006-08-19' Union All
Select 'C','2006-08-15' Union All
Select 'C','2006-08-20'
GO
select * from testDeclare @S Varchar(8000)
Select @S=''Select @S=@S+',SUM(Case State When '''+convert(varchar(10),State,120)+'''Then 1 Else 0 End) As '+'['+convert(varchar(10),State,120)+']'
From TEST Group By State Order By State
Select @S='Select Type'+@S+' From TEST Group By Type'
EXEC(@S)
(Type Char(1),
State datetime)
Insert TEST
Select 'A','2006-5-10' Union All
Select 'A','2006-5-1' Union All
Select 'A','2006-5-9' Union All
Select 'B','2006-5-1' Union All
Select 'B','2006-5-5' Union All
Select 'B','2006-5-1' Union All
Select 'C','2006-5-5' Union All
Select 'C','2006-5-9'
--select * from test
GO
--State不固定的情況下的
Declare @S Varchar(8000)
Select @S=''
Select @S=@S+',SUM(Case State When '''+convert(varchar(10),State,120)+ '''Then 1 Else 0 End) As ' + '[' + convert(varchar(10),State,120) + ']'
From TEST Group By State Order By State
Select @S='Select Type'+@S+' From TEST Group By Type'
EXEC(@S)
Drop Table TEST
GO