求一函数

[山野市民]

一张表
标签表
TagID Name GroupID GroupIndex
1 温度1 1 1
2 温度2 1 2
3 温度3 1 3
4 湿度1 2 1
5 湿度2 2 2
6 灰尘1 3 1
7 灰尘2 3 2
记录表
LogID TagID SN Value
1 1 T1 30
2 4 T1 31
3 2 T1 32
4 3 T1 33
5 6 T1 34
6 5 T1 35
7 1 T2 36
8 3 T2 37
9 4 T2 30
10 7 T2 12
结果
SN 温度1 温度2 温度3 湿度1 湿度2 灰尘1 灰尘2
T1 30 32 33 31 35 34 NULL
T2 36 NULL 37 30 NULL NULL 12

备注：结果表的列名必须先按GroupID（从大到小）排序在按GroupIndex排序（从大到小）

[yanglinqiang]

哥们结贴吧。用游标搞定

declare @sq1 nvarchar(4000)
declare @sq2 nvarchar(1000)
declare @sq3 nvarchar(1000)
declare @i int
declare @Tagid int
declare @name varchar(50)
set @i=0
set @sq1='select distinct(a.SN)'
set @sq2='t2 a '
set @sq3=' from'
declare c_people cursor for select TagID,[Name] from t1 order by GroupID,GroupIndex
open c_people
fetch next from c_people into @Tagid,@name
while @@fetch_status=0
begin
set @sq1=@sq1+',b'+cast(@i as varchar (10))+'.Value as '+@name
set @sq2='('+@sq2+'left join t2 b'+cast(@i as varchar (10))+' on b'+cast(@i as varchar (10))+'.TagID='+cast(@Tagid as varchar(10))+' and b'+cast(@i as varchar (10))+'.SN=a.SN)'
set @i=@i+1
fetch next from c_people into @Tagid,@name
end
close c_people
deallocate c_people
set @sq1=@sq1+@sq3+@sq2
print(@sq1)
EXEC(@sq1)

[yanglinqiang]

declare @sq1 nvarchar(4000)
declare @sq2 nvarchar(1000)
declare @sq3 nvarchar(1000)
declare @i int
declare @Tagid int
declare @name varchar(50)
set @i=0
set @sq1='select distinct(a.SN)'
set @sq2='t2 a '
set @sq3=' from'
declare c_people cursor for select TagID,[Name] from t1 order by GroupID,GroupIndex
open c_people
fetch next from c_people into @Tagid,@name
while @@fetch_status=0
begin
set @sq1=@sq1+',b'+cast(@i as varchar (10))+'.Value as '+@name
set @sq2='('+@sq2+'left join t2 b'+cast(@i as varchar (10))+' on b'+cast(@i as varchar (10))+'.TagID='+cast(@Tagid as varchar(10))+' and b'+cast(@i as varchar (10))+'.SN=a.SN)'
set @i=@i+1
fetch next from c_people into @Tagid,@name
end
close c_people
deallocate c_people
set @sq1=@sq1+@sq3+@sq2
print(@sq1)
EXEC(@sq1)

[zhouxingyu896]

楼主的结帖率太强了

用SQL语句进行
或者用集合中的Sort方法

[mbh0210]

--标签表 
declare @t1 table(TagID int,[Name] varchar(20),  GroupID int ,  GroupIndex int )
insert into @t1 values(1  ,  '温度1'  ,  1      ,  1 )
insert into @t1 values(2  ,  '温度2'  ,  1      ,  2 )
insert into @t1 values(3  ,  '温度3'  ,  1      ,  3 )
insert into @t1 values(4  ,  '湿度1'  ,  2      ,  1 )
insert into @t1 values(5  ,  '湿度2'  ,  2      ,  2 )
insert into @t1 values(6  ,  '灰尘1'  ,  3      ,  1 )
insert into @t1 values(7  ,  '灰尘2'  ,  3      ,  2 )
--记录表 
declare @t2 table(LogID int , TagID int ,  SN varchar(20) ,    Value int)
insert into @t2 values(1  ,  1  ,  'T1'  ,  30 )
insert into @t2 values(2  ,  4  ,  'T1'  ,  31 )
insert into @t2 values(3  ,  2  ,  'T1'  ,  32 )
insert into @t2 values(4  ,  3  ,  'T1'  ,  33 )
insert into @t2 values(5  ,  6  ,  'T1'  ,  34 )
insert into @t2 values(6  ,  5  ,  'T1'  ,  35 )
insert into @t2 values(7  ,  1  ,  'T2'  ,  36 )
insert into @t2 values(8  ,  3  ,  'T2'  ,  37 )
insert into @t2 values(9  ,  4  ,  'T2'  ,  30 )
insert into @t2 values(10 ,  7  ,  'T2'  ,  12 )


select SN,
sum(case when a.TagID=1 then Value else null end )  as 温度1,
sum(case when a.TagID=2 then Value else null end )  as 温度2,
sum(case when a.TagID=3 then Value else null end )  as 温度3,
sum(case when a.TagID=4 then Value else null end )  as 湿度1,
sum(case when a.TagID=5 then Value else null end )  as 湿度2,
sum(case when a.TagID=6 then Value else null end )  as 灰尘1,
sum(case when a.TagID=7 then Value else null end )  as 灰尘2
 from @t2 a 
inner join @t1 b on b.TagID = a.TagID
group by SN

[starj1]

结贴能超过100%?牛啊

[mywisest]

行列转换.