请教一个sql语句

Question

我有一张表是这样存储的id     value     time
1        1a      1:00:00
1        1c      3:00:00
1        1d      4:00:00
1        1e      5:00:00
2        2a      1:00:00
2        2b      2:00:00
2        2d      4:00:00
2        2e      5:00:00
3        3a      1:00:00
3        3b      2:00:00
3        3c      3:00:00
3        3e      5:00:00
........
..........也就是在某个时间点，并不是所有id都有数据，我要怎么查询才能查到下面的结果啊？就是查找某个时间段里面所有id的value，这条语句应该怎么写？id1     id2     id3       time
1a       2a      3a      1:00:00
NULL     2b      3b      2:00:00
1c       NULL    3c      3:00:00
1d       2d      NULL    4:00:00
1e       2e      3e      5:00:00

Answer 1

use db;declare @t table(id int,value varchar(10),time varchar(10));
insert into @t values
(1, '1a', '1:00:00'),
(1, '1c', '3:00:00'),
(1, '1d', '4:00:00'),
(1, '1e', '5:00:00'),
(2, '2a', '1:00:00'),
(2, '2b', '2:00:00'),
(2, '2d', '4:00:00'),
(2, '2e', '5:00:00'),
(3, '3a', '1:00:00'),
(3, '3b', '2:00:00'),
(3, '3c', '3:00:00'),
(3, '3e', '5:00:00');select [1] as[id1] ,[2] as [id2],[3] as [id3],time from @t a pivot(max(value) for id in ([1],[2],[3])) pt
/*
id1        id2        id3        time
---------- ---------- ---------- ----------
1a         2a         3a         1:00:00
NULL       2b         3b         2:00:00
1c         NULL       3c         3:00:00
1d         2d         NULL       4:00:00
1e         2e         3e         5:00:00
*/这个是很经典的行列转化，可以在精华贴里找得到的。

Answer 2

--> 测试数据：[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT,[value] VARCHAR(2),[time] DATETIME)
INSERT [tb]
SELECT 1,'1a','1:00:00' UNION ALL
SELECT 1,'1c','3:00:00' UNION ALL
SELECT 1,'1d','4:00:00' UNION ALL
SELECT 1,'1e','5:00:00' UNION ALL
SELECT 2,'2a','1:00:00' UNION ALL
SELECT 2,'2b','2:00:00' UNION ALL
SELECT 2,'2d','4:00:00' UNION ALL
SELECT 2,'2e','5:00:00' UNION ALL
SELECT 3,'3a','1:00:00' UNION ALL
SELECT 3,'3b','2:00:00' UNION ALL
SELECT 3,'3c','3:00:00' UNION ALL
SELECT 3,'3e','5:00:00'
GO--> 测试语句：
SELECT max(case when [id]=1 then [value] else null end) as [id1],
 max(case when [id]=2 then [value] else null end) as [id2],
 max(case when [id]=3 then [value] else null end) as [id3],
 [time]
FROM [tb]
group by [time]
/*
id1  id2  id3  time
---- ---- ---- -----------------------
1a   2a   3a   1900-01-01 01:00:00.000
NULL 2b   3b   1900-01-01 02:00:00.000
1c   NULL 3c   1900-01-01 03:00:00.000
1d   2d   NULL 1900-01-01 04:00:00.000
1e   2e   3e   1900-01-01 05:00:00.000
*/

Answer 3

--动态
declare @s varchar(8000)
select @s=isnull(@s+',','')+quotename([id])+'=max(case when [id]='+quotename([id],'''')+' then [value] else null end)'
from tb group by [id]
exec('select '+@s+' ,[time] from tb group by [time]')
/*
1    2    3    time
---- ---- ---- -----------------------
1a   2a   3a   1900-01-01 01:00:00.000
NULL 2b   3b   1900-01-01 02:00:00.000
1c   NULL 3c   1900-01-01 03:00:00.000
1d   2d   NULL 1900-01-01 04:00:00.000
1e   2e   3e   1900-01-01 05:00:00.000
警告: 聚合或其他 SET 操作消除了 Null 值。
*/

Answer 4

--2005 动态declare @s varchar(8000)
select @s=isnull(@s+',','')+quotename([id]) from tb group by[id]
exec('select * from tb pivot (max([value]) for [id] in('+@s+'))b')

Answer 5

select [1] as[id1] ,[2] as [id2],[3] as [id3],time from @t a pivot(max(value) for id in ([1],[2],[3])) pt

Answer 6

create table test(id int,name varchar(20),quarter int,profile int) 
insert into test values(1,'a',1,1000)
insert into test values(1,'a',2,2000)
insert into test values(1,'a',3,4000)
insert into test values(1,'a',4,5000)
insert into test values(2,'b',1,3000)
insert into test values(2,'b',2,3500)
insert into test values(2,'b',3,4200)
insert into test values(2,'b',4,5500)select id,name,
[1] as "一季度",
[2] as "二季度",
[3] as "三季度",
[4] as "四季度"
from
test
pivot
(
sum(profile)
for quarter in
([1],[2],[3],[4])
)
as pvtdrop table testcreate table test(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int)insert into test values(1,'a',1000,2000,4000,5000)
insert into test values(2,'b',3000,3500,4200,5500)
select * from testselect id,name,quarter,profile
from
test
unpivot
(
profile
for quarter in
([Q1],[Q2],[Q3],[Q4])
) 
as unpvt

Answer 7

--sql server 2000静态
-- 测试数据：[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT,[value] VARCHAR(2),[time] DATETIME)
INSERT [tb]
SELECT 1,'1a','1:00:00' UNION ALL
SELECT 1,'1c','3:00:00' UNION ALL
SELECT 1,'1d','4:00:00' UNION ALL
SELECT 1,'1e','5:00:00' UNION ALL
SELECT 2,'2a','1:00:00' UNION ALL
SELECT 2,'2b','2:00:00' UNION ALL
SELECT 2,'2d','4:00:00' UNION ALL
SELECT 2,'2e','5:00:00' UNION ALL
SELECT 3,'3a','1:00:00' UNION ALL
SELECT 3,'3b','2:00:00' UNION ALL
SELECT 3,'3c','3:00:00' UNION ALL
SELECT 3,'3e','5:00:00'
GO
-- 测试语句：
SELECT max(case when [id]=1 then [value] else null end) as [id1],
 max(case when [id]=2 then [value] else null end) as [id2],
 max(case when [id]=3 then [value] else null end) as [id3],
 [time]
FROM [tb]
group by [time]
/*
id1  id2  id3  time
---- ---- ---- -----------------------
1a   2a   3a   1900-01-01 01:00:00.000
NULL 2b   3b   1900-01-01 02:00:00.000
1c   NULL 3c   1900-01-01 03:00:00.000
1d   2d   NULL 1900-01-01 04:00:00.000
1e   2e   3e   1900-01-01 05:00:00.000
*/-sql server 2000动态
declare@sqlvarchar(8000)set@sql='select id'select@sql=@sql+',max(case when [id]='+quotename([id],'''') then[value] else null end)['+quotename([id],'''')']'from (select distinct [id] from [tb])a--同from tb group by id，默认按id排序set@sql=@sql+' from tb group by id'exec(@sql)/*
1    2    3    time
---- ---- ---- -----------------------
1a   2a   3a   1900-01-01 01:00:00.000
NULL 2b   3b   1900-01-01 02:00:00.000
1c   NULL 3c   1900-01-01 03:00:00.000
1d   2d   NULL 1900-01-01 04:00:00.000
1e   2e   3e   1900-01-01 05:00:00.000
警告: 聚合或其他 SET 操作消除了 Null 值。
*/
--sql server 2005 静态
select * from tb pivot (max([value]) for [id] in([1],[2],[3]))b
--sql  server 2005 动态declare @s varchar(8000)
select @s=isnull(@s+',','')+quotename([id]) from tb group by [id]
exec('select * from tb pivot (max([value]) for [id] in('+@s+'))b')