救命啊！求一SQL语句！

[山野市民]

一张表
标签表
TagID Name
1 温度1
2 温度2
3 温度3
4 湿度1
5 湿度2
6 灰尘1
7 灰尘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

这样的SQL语句怎么写啊！或者在.net语句也可以

[izbox]

行转列！

[华夏小卒]

if object_id('标签表')is not null drop table 标签表
go
CREATE TABLE 标签表(TagID INT, Name VARCHAR(10))
INSERT 标签表 SELECT
1, '温度1'UNION ALL SELECT 
2, '温度2'UNION ALL SELECT 
3, '温度3' UNION ALL SELECT
4, '湿度1' UNION ALL SELECT
5, '湿度2' UNION ALL SELECT
6, '灰尘1' UNION ALL SELECT
7, '灰尘2' 
if object_id('记录表')is not null drop table 记录表
go
CREATE TABLE 记录表(LogID INT,TagID INT,SN  VARCHAR(10),[Value] INT)
INSERT 记录表 SELECT
1, 1 ,'T1', 30 UNION ALL SELECT
2, 4 ,'T1', 31 UNION ALL SELECT
3, 2 ,'T1', 32 UNION ALL SELECT
4, 3 ,'T1', 33 UNION ALL SELECT
5, 6 ,'T1', 34 UNION ALL SELECT
6, 5 ,'T1', 35 UNION ALL SELECT
7, 1 ,'T2', 36 UNION ALL SELECT
8, 3 ,'T2', 37 UNION ALL SELECT
9, 4 ,'T2',30 UNION ALL SELECT
10, 7, 'T2', 12 

DECLARE @S VARCHAR(4000)

SELECT @S=ISNULL(@S+',','')+'MAX(CASE WHEN TagID='+LTRIM(TagID)+' THEN [VALUE] ELSE NULL END) ['+NAME+']'
FROM 标签表

SET @S='SELECT SN,'+@S+' FROM 记录表 GROUP BY SN'

EXEC(@S)

SN         温度1         温度2         温度3         湿度1         湿度2         灰尘1         灰尘2
---------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
T1         30          32          33          31          35          34          NULL
T2         36          NULL        37          30          NULL        NULL        12
警告: 聚合或其他 SET 操作消除了空值。

(2 行受影响)

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-09-27 09:37:13
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
--	Nov 24 2008 13:01:59 
--	Copyright (c) 1988-2005 Microsoft Corporation
--	Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[标签表]
if object_id('[标签表]') is not null drop table [标签表]
go 
create table [标签表]([TagID] int,[Name] varchar(5))
insert [标签表]
select 1,'温度1' union all
select 2,'温度2' union all
select 3,'温度3' union all
select 4,'湿度1' union all
select 5,'湿度2' union all
select 6,'灰尘1' union all
select 7,'灰尘2'
--> 测试数据：[记录表]
if object_id('[记录表]') is not null drop table [记录表]
go 
create table [记录表]([LogID] int,[TagID] int,[SN] varchar(2),[Value] int)
insert [记录表]
select 1,1,'T1',30 union all
select 2,4,'T1',31 union all
select 3,2,'T1',32 union all
select 4,3,'T1',33 union all
select 5,6,'T1',34 union all
select 6,5,'T1',35 union all
select 7,1,'T2',36 union all
select 8,3,'T2',37 union all
select 9,4,'T2',30 union all
select 10,7,'T2',12
--------------开始查询--------------------------
declare @sql varchar(8000)
set @sql = 'select b.SN '
select @sql = @sql + ' , max(case a.[Name] when ''' + a.[Name] + ''' then b.[Value] else 0 end) [' + [Name] + ']'
from (select distinct a.[Name] from [标签表] a) as a
set @sql = @sql + ' from [标签表] a,[记录表] b where a.[TagID]=b.[TagID] group by b.SN'
exec(@sql) 
----------------结果----------------------------
/* SN   灰尘1         灰尘2         湿度1         湿度2         温度1         温度2         温度3
---- ----------- ----------- ----------- ----------- ----------- ----------- -----------
T1   34          0           31          35          30          32          33
T2   0           12          30          0           36          0           37

(2 行受影响)

*/