NAME Value1 Value2 Type
张三 11 22 A
张三 33 44 B
张三 55 66 C
张三 11 99 E
李四 10 20 A
李四 30 40 B
李四 50 60 C
李四 70 80 D
...条件:
1 TYPE有五种 A B C D E 类型,如果某个人没有那种类型,则默认为0想得到结果:统计出该表所有不同用户的记录
NAME A_Value1 A_Value2 B_Value1 B_Value2 C_1 C_2 D_1 D_2 E_1 E_2
张三 11 22 33 44 55 66 0 0 11 99
李四 10 20 30 40 50 60 70 80 0 0
...约束:
1 SQL语句中不能有where NAME = '张三'
2 语法为sqlserver或则access
解决方案 »
- ORACLE小问题
- 在sql server 2000中,有两个datatime类型的字段,我想得到它们的时间差,怎么办?
- 如何将char转换成uniqueidentifier类型?
- select读取带有参数的存储过程返回集,求读取语句
- Select * from EduManager where EMName='110110' And Password='110110' 为什么这句会错呢??
- sql中前几个月的查询?
- 关于一个SQLServer2005配置管理器的一个问题,麻烦请高手进来一下!!!
- 打开企业管理器,双击(LOCAL)(windows NT)提示‘未能建立与(LOCAL)的连接’原因:用户‘(null)……
- 如何可以计算出该问题中的自定义函数结果?
- 各位大侠,请教一个触发器的问题,插入记录时根据主键判断记录是否存在,如果存在,则更新记录,如果不存在,则插入记录的触发器怎么写,
- sql提问,问题比较麻烦
- 求平均值,急急
declare @tb TABLE([NAME] nvarchar(100),Value1 int,Value2 int ,type nvarchar(1))
insert @tb select '张三',11, 22,'A'
union all select '张三',33 ,44,'B'
union all select '张三',55 ,66,'C'
union all select '张三',11 ,99,'E'
union all select '李四',10 ,20,'A'
union all select '李四',30 ,40,'B'
union all select '李四',50 ,60,'C'
union all select '李四',70 ,80,'D'
select * from @tbselect distinct a.NAME ,
isnull((select value1 from @tb where [name]=a.[name] and type='A' ),0)A_Value1,
isnull((select value2 from @tb where [name]=a.[name] and type='A' ),0) A_Value2,
isnull((select value1 from @tb where [name]=a.[name] and type='B' ),0) B_Value1,
isnull((select value2 from @tb where [name]=a.[name] and type='B' ),0) B_Value2,
isnull((select value1 from @tb where [name]=a.[name] and type='C' ),0) C_1,
isnull((select value2 from @tb where [name]=a.[name] and type='C' ),0) C_2,
isnull((select value1 from @tb where [name]=a.[name] and type='D' ),0)D_1,
isnull((select value2 from @tb where [name]=a.[name] and type='D' ),0) D_2,
isnull((select value1 from @tb where [name]=a.[name] and type='E' ),0) E_1,
isnull((select value2 from @tb where [name]=a.[name] and type='E' ),0) E_2
from @tb a 结果NAME A_Value1 A_Value2 B_Value1 B_Value2 C_1 C_2 D_1 D_2 E_1 E_2
李四 10 20 30 40 50 60 70 80 0 0
张三 11 22 33 44 55 66 0 0 11 99
sum(case when type='A' then value1 else 0 end) as A_1,
sum(case when type='A' then value2 else 0 end) as A_2,
sum(case when type='B' then value1 else 0 end) as B_1,
sum(case when type='B' then value2 else 0 end) as B_2,
sum(case when type='C' then value1 else 0 end) as C_1,
sum(case when type='C' then value2 else 0 end) as C_2,
sum(case when type='D' then value1 else 0 end) as D_1,
sum(case when type='D' then value2 else 0 end) as D_2,
sum(case when type='E' then value1 else 0 end) as E_1,
sum(case when type='E' then value2 else 0 end) as E_2
from tablename
group by name
access把case when改成iif
insert tb select '张三',11, 22,'A'
union all select '张三',33 ,44,'B'
union all select '张三',55 ,66,'C'
union all select '张三',11 ,99,'E'
union all select '李四',10 ,20,'A'
union all select '李四',30 ,40,'B'
union all select '李四',50 ,60,'C'
union all select '李四',70 ,80,'D'
declare @sql varchar(2000)
set @sql='select NAME, '
select @sql=@sql+'sum(case when type='''+a.type+''' then Value1 else 0 end )as '+a.type+'_Value , '+'sum(case when type='''+a.type+''' then Value2 else 0 end)as '+a.type+'_value'+','
from (select type from tb group by type)a
set @sql=left(@sql,len(@sql)-1)+' from tb group by NAME'exec (@sql)