表结构id name count time
1 a 12 2007-2-7
2 b 13 2007-2-7
3 c 4 2007-2-7
4 d 15 2007-2-7
5 e 7 2007-2-7
6 a 21 2007-2-8
7 b 33 2007-2-8
8 c 11 2007-2-8
9 d 65 2007-2-8
10 e 10 2007-2-8期望结果:
2007-2-7 2007-2-8
a 12 21
b 13 33
c 4 11
d 15 65
e 7 10
谢谢
1 a 12 2007-2-7
2 b 13 2007-2-7
3 c 4 2007-2-7
4 d 15 2007-2-7
5 e 7 2007-2-7
6 a 21 2007-2-8
7 b 33 2007-2-8
8 c 11 2007-2-8
9 d 65 2007-2-8
10 e 10 2007-2-8期望结果:
2007-2-7 2007-2-8
a 12 21
b 13 33
c 4 11
d 15 65
e 7 10
谢谢
解决方案 »
- php连接mssql2005怎么配置
- 多表查询问题
- 为什么用 exec master..xp_cmdshell 'dir D:\Program Files' 看不到文件或文件夹?
- 核实一条SQL语句,关于TOP和SUM,我怎么得到的只有一条,而本论坛的同仁怎么说是好多条???
- 转换 varchar 值 '13811110001' 时溢出了整数列。超过了其中最大的整数值。
- 请问sql2000里哪个数据类型是自动编号??
- MSSQL Server 2000问题
- 100分求:同步更新的存储过程应该怎么写?
- 怎样取得网卡的标识号(ID)?
- ◎◎___JDBC与存储过程的问题,高手请进!
- 还要再次麻烦大家,谢谢!
- 初学者的一个问题~
INSERT INTO @tb
SELECT 1, 'a', 12, '2007-2-7'
UNION ALL SELECT 2, 'b', 13, '2007-2-7'
UNION ALL SELECT 3, 'c', 4, '2007-2-7'
UNION ALL SELECT 4, 'd', 15, '2007-2-7'
UNION ALL SELECT 5, 'e', 7, '2007-2-7'
UNION ALL SELECT 6, 'a', 21, '2007-2-8'
UNION ALL SELECT 7, 'b', 33, '2007-2-8'
UNION ALL SELECT 8, 'c', 11, '2007-2-8'
UNION ALL SELECT 9, 'd', 65, '2007-2-8'
UNION ALL SELECT 10, 'e', 10, '2007-2-8'SELECT [name], SUM(CASE WHEN [TIME] = '2007/2/7' THEN [count] ELSE 0 END)
, SUM(CASE WHEN [TIME] = '2007/2/8' THEN [count] ELSE 0 END)
FROM @tb
GROUP BY [name]/*结果
a 12 21
b 13 33
c 4 11
d 15 65
e 7 10
*/
DECLARE @tb TABLE([id] int, [name] varchar(10), [count] int, [time] datetime)
INSERT INTO @tb
SELECT 1, 'a', 12, '2007-2-7'
UNION ALL SELECT 2, 'b', 13, '2007-2-7'
UNION ALL SELECT 3, 'c', 4, '2007-2-7'
UNION ALL SELECT 4, 'd', 15, '2007-2-7'
UNION ALL SELECT 5, 'e', 7, '2007-2-7'
UNION ALL SELECT 6, 'a', 21, '2007-2-8'
UNION ALL SELECT 7, 'b', 33, '2007-2-8'
UNION ALL SELECT 8, 'c', 11, '2007-2-8'
UNION ALL SELECT 9, 'd', 65, '2007-2-8'
UNION ALL SELECT 10, 'e', 10, '2007-2-8'--select * from @tbdeclare @sql varchar(8000)
set @sql='select name'
select @sql=@sql+',sum(case time when '''+convert(varchar(10),[time],120)+''' then [count] end) ['+convert(varchar(10),[time],120)+']' from (select distinct [time] from @tb) a
select @sql=@sql+' from @tb group by name'
--print @sql
exec(@sql)
name '2007-02-07''2007-02-08'
a 12 21
b 13 33
c 4 11
d 15 65
e 7 10
create TABLE #tb ([id] int, [name] varchar(10), [count] int, [time] datetime)
INSERT INTO #tb
SELECT 1, 'a', 12, '2007-2-7'
UNION ALL SELECT 2, 'b', 13, '2007-2-7'
UNION ALL SELECT 3, 'c', 4, '2007-2-7'
UNION ALL SELECT 4, 'd', 15, '2007-2-7'
UNION ALL SELECT 5, 'e', 7, '2007-2-7'
UNION ALL SELECT 6, 'a', 21, '2007-2-8'
UNION ALL SELECT 7, 'b', 33, '2007-2-8'
UNION ALL SELECT 8, 'c', 11, '2007-2-8'
UNION ALL SELECT 9, 'd', 65, '2007-2-8'
UNION ALL SELECT 10, 'e', 10, '2007-2-8'--select * from @tb
--select name,sum(case time when '2007-02-07' then [count] end) [2007-02-07],sum(case time when '2007-02-08' then [count] end) [2007-02-08] from @tb group by name
declare @sql varchar(8000)
set @sql='select name'
select @sql=@sql+',sum(case time when '''+convert(varchar(10),[time],120)+''' then [count] end) ['+convert(varchar(10),[time],120)+']' from (select distinct [time] from #tb) a
select @sql=@sql+' from #tb group by name'
print @sql
exec(@sql)
drop table #tb
INSERT INTO TB
SELECT 1, 'a', 12, '2007-2-7'
UNION ALL SELECT 2, 'b', 13, '2007-2-7'
UNION ALL SELECT 3, 'c', 4, '2007-2-7'
UNION ALL SELECT 4, 'd', 15, '2007-2-7'
UNION ALL SELECT 5, 'e', 7, '2007-2-7'
UNION ALL SELECT 6, 'a', 21, '2007-2-8'
UNION ALL SELECT 7, 'b', 33, '2007-2-8'
UNION ALL SELECT 8, 'c', 11, '2007-2-8'
UNION ALL SELECT 9, 'd', 65, '2007-2-8'
UNION ALL SELECT 10, 'e', 10, '2007-2-8'DECLARE @SQL VARCHAR(8000)
DECLARE @date DATETIME
DECLARE @total INT
DECLARE @I INT
SET @SQL = 'SELECT [name]'
SET @date = '2007/2/1'
SET @total = 32 - DAY(@date + 32 - DAY(@date))
SET @I = 0
WHILE @I < @total
BEGIN
SET @SQL = @SQL + ', SUM(CASE WHEN [TIME] = ''' + CONVERT(VARCHAR(10), DATEADD(DAY, @I, @date), 112)
+ ''' THEN [count] ELSE 0 END) AS [' + CONVERT(VARCHAR(10), DATEADD(DAY, @I, @date), 112) + ']'
SET @I = @I + 1
ENDEXEC(@SQL + ' FROM TB GROUP BY [name]')DROP TABLE TB
(
id int identity(1,1),
name varchar(20),
[count] int,
[time] datetime
)insert into C2R(name,count,time)
select 'A',10,getdate()
union all
select 'B',20,getdate()
union all
select 'C',11,getdate()
union all
select 'A',10,dateadd(day,-1,getdate())
union all
select 'B',20,dateadd(day,-2,getdate())
union all
select 'C',11,dateadd(day,-1,getdate())
Declare @SQL varchar(8000)
set @SQL ='select name'
select @SQL =@SQL + ','+quotename(convert(char(10),time,120))+'=sum(case convert(char(10),time,120) when '''+ convert(char(10),time,120) +''' then Count else 0 end )'
from(select distinct(time) from C2R )Aset @SQL =@SQL +' from C2R group by name '
print @SQL
exec(@SQL)
drop table C2R