一个关于char的交叉表建立

[jacobwc]

有这样的表
code value date
a001 10 2009-12-01
a002 阳性 2009-12-01
a003 ++ 2009-12-11
a001 7 2009-12-2

需求这样显示

a001 a002 a003
2009-12-01 10 阳性
2009-12-11 7 ++

如何处理这样字符型的数据。。。一般的交叉表一定要用sum()...
高分请教!!!!

[让你望见影子的墙]

行列转换，sum换为max

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2010-01-14 10:48:15
-- 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)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([code] varchar(4),[value] varchar(4),[date] datetime)
insert [tb]
select 'a001','10','2009-12-01' union all
select 'a002','阳性','2009-12-01' union all
select 'a003','++','2009-12-11' union all
select 'a001','7','2009-12-11'
--------------开始查询--------------------------
select
 convert(varchar(10),date,120), 
 max(case code when 'a001' then [value] else '' end) as 'a001',
 max(case code when 'a002' then [value] else '' end) as 'a002',
 max(case code when 'a003' then [value] else '' end) as 'a003'
from
 [tb]
group by
 convert(varchar(10),date,120)
----------------结果----------------------------
/*            a001 a002 a003
---------- ---- ---- ----
2009-12-01 10   阳性   
2009-12-11 7         ++

(2 行受影响)


*/

[xiequan2]

看需求，这里改为max，你统计的值没有求和;

如果你用pivot,必须要用聚合函数,语法要求,

一般的case when 的行列转换,max其实就是为了除去'',null;取决于你的 case when 里else的写法;

[黄_瓜]

--> 测试数据：t1
create table t1 ([code] varchar(4),[value] varchar(4),[date] datetime)
insert t1
select 'a001','10','2009-12-01' union all
select 'a002','阳性','2009-12-01' union all
select 'a003','++','2009-12-11' union all
select 'a001','7','2009-12-2'

declare @s varchar(8000)
set @s='select date'
select @s=@s+',case when code='''+code+''' then value else null end ['+code+']'
from (select distinct code from t1) t
set @s=@s+' from t1'
exec(@s)
/*
date                    a001 a002 a003
----------------------- ---- ---- ----
2009-12-01 00:00:00.000 10   NULL NULL
2009-12-01 00:00:00.000 NULL 阳性   NULL
2009-12-11 00:00:00.000 NULL NULL ++
2009-12-02 00:00:00.000 7    NULL NULL
*/

[黄_瓜]

--> 测试数据：@tb
create table t1 ([code] varchar(4),[value] varchar(4),[date] datetime)
insert t1
select 'a001','10','2009-12-01' union all
select 'a002','阳性','2009-12-01' union all
select 'a003','++','2009-12-11' union all
select 'a001','7','2009-12-2'

declare @s varchar(8000)
set @s='select date'
select @s=@s+',max(case when code='''+code+''' then value else '''' end) ['+code+']'
from (select distinct code from t1) t
set @s=@s+' from t1 group by date '
exec(@s)
/*
date                    a001 a002 a003
----------------------- ---- ---- ----
2009-12-01 00:00:00.000 10   阳性   
2009-12-02 00:00:00.000 7         
2009-12-11 00:00:00.000           ++

*/