Question

高分求助，SQL查询语句

表Data 字段datetime,name,ljll1,ljll2

2012-04-18 09:00:00,对象1,14922380,NULL
2012-04-18 09:00:00,对象2,244,28729890
2012-04-18 09:00:00,对象3,3532815.4,2386495.3
2012-04-18 09:00:00,对象4,12861520,7408951
2012-04-18 09:00:00,对象5 ,????,????
2012-04-18 10:00:00,对象1,14922930,NULL
2012-04-18 10:00:00,对象2，244，28730950
2012-04-18 10:00:00,对象3,3532815.4,2386495.3
2012-04-18 10:00:00,对象4,12861909,7408951
2012-04-18 10:00:00,对象5,????,????
2012-04-18 11:00:00,对象1,14923490,NULL
2012-04-18 11:00:00,对象2,244,28731990
2012-04-18 11:00:00,对象3,3532815.4,2386495.3
2012-04-18 11:00:00,对象4,12862421,7408951
2012-04-18 11:00:00,对象5,????,????
-------------------------------------------------------------------
想要的查询结果：
datetima 对象1,ljll1,ljll2,对象2,ljll1,ljll2,对象3,ljll1,ljll2,对象4,ljll1,ljll2,对象5,ljll1,ljll2.
其中ljll1,ljll2是各自时间、对象中的各自数据
表中的对象不定时，会增加如增加，对象678..等
需要的结果：
时间,对象1_ljll1 ,对象1_ljll2,对象2_ljll1,对象2_ljll2,对象3_ljll1,对象3_ljll2,...
如：
2012-04-18 09:00:00,14922380,NULL,244,28729890,3532815.4,2386495.3,...
2012-04-18 10:00:00,14922930,NULL,244,28730950,3532815.4,2386495.3 ...
2012-04-18 11:00:00,14923490,NULL,244,28731990,3532815.4,2386495.3,...

谢谢..

Answer 1

select datetime ,
max(case name when '对象1' then ljll1 else 0 end) 对象1ljll1,
max(case name when '对象1' then ljll2 else 0 end) 对象1ljll2,
max(case name when '对象2' then ljll1 else 0 end) 对象2ljll1,
max(case name when '对象2' then ljll2 else 0 end) 对象2ljll2,
max(case name when '对象3' then ljll1 else 0 end) 对象3ljll1,
max(case name when '对象3' then ljll2 else 0 end) 对象3ljll2,
max(case name when '对象4' then ljll1 else 0 end) 对象4ljll1,
max(case name when '对象4' then ljll2 else 0 end) 对象4ljll2,
max(case name when '对象5' then ljll1 else 0 end) 对象5ljll1,
max(case name when '对象5' then ljll2 else 0 end) 对象5ljll2
from table
group by datetime

你是要这个吧

追问

现在加入表中记录出现了对象6,这条语句就不太合适了
数据表中对象会不定时，增加对象。能否根据表中的name对象。
动态查询？？？？

追答

用 ”迷人的反派“的回答就行

Answer 2

这是别人给我的语句,我改了一下,你试试能不能用
DECLARE @s VARCHAR(8000)
SELECT @s=isnull(@s+',','')+'max(case when name='''+ name +''' then ljll1 end) as ['+name+'_'+'ljll1'+']'
+', max(case when name='''+ name +''' then ljll2 end) as ['+name+'_'+'ljll2'+']'
FROM (SELECT DISTINCT name FROM Data) j
PRINT @s
EXEC ('select datetime,'+@s+' from Data group by datetime')

Answer 3

create function dbo.fun(@data varchar(200))
returns varchar(3000)
as
begin
declare @s varchar(3000)
set @s= ' '
select @s=@s + ',' +stuff(@data,1,20,'')
from tb where left(data,20)=left(@data,20)
return stuff(@s,1,1, ' ')
end
go

select dbo.fun(data) txt from tb group by left(data,20)

Answer 4

行边列
用case when