A表是这样的:
人员表(Clerk):
id Name
1 AAA
2 BBB
3 CCC
规格表(Spec)
clerkId Model Spec
1 m1 s11
1 m2 s21
1 m3 s31
2 m1 s12
2 m2 s22
2 m3 s32
3 m1 s13
3 m2 s23
3 m3 s33
我想要一个SQL得到如下结果:
Clerk m1 m2 m3
1 s11 s21 s31
2 s12 s22 s32
3 s13 s23 s33由于普通的交叉,下面Spec应该是数字,可以直接用sum来实现交叉,而这种不是数字的交叉应该如何实现?
人员表(Clerk):
id Name
1 AAA
2 BBB
3 CCC
规格表(Spec)
clerkId Model Spec
1 m1 s11
1 m2 s21
1 m3 s31
2 m1 s12
2 m2 s22
2 m3 s32
3 m1 s13
3 m2 s23
3 m3 s33
我想要一个SQL得到如下结果:
Clerk m1 m2 m3
1 s11 s21 s31
2 s12 s22 s32
3 s13 s23 s33由于普通的交叉,下面Spec应该是数字,可以直接用sum来实现交叉,而这种不是数字的交叉应该如何实现?
解决方案 »
- col1省份代码 col2客户代码 想把两列合并作为col3 update 湖北 set col3=col1||col2 怎么结果变成了'col1||col2'
- 将查询结果显示在一行
- 急!!求一SQL语句的写法。
- 如何获取exec()的返回结果
- asp调用存储过程难题,盼解答。万分感谢
- SQL语句的写法!!!!谢谢!!!!!!!!!!
- 配置过Windows 2000 Advanced Server 中的群集管理器的高手请进来
- 我的bcp为什么会出错?
- 您好,关于在SQL SERVER上的management->agent->jobs上执行EXE文件的问题?
- 有关复制的一个简单问题
- 求一Sql语句
- 请教关于表的操作
max(case when Model='m1' then Spec else '' end) as 'm1',
max(case when Model='m2' then Spec else '' end) as 'm2',
max(case when Model='m3' then Spec else '' end) as 'm3'
from Spec
group by clerkId
--创建测试环境
create table Spec(clerkId int,Model char(2),Spec char(3))
insert into Spec
select 1,'m1','s11'
union all select 1,'m2','s21'
union all select 1,'m3','s31'
union all select 2,'m1','s12'
union all select 2,'m2','s22'
union all select 2,'m3','s32'
union all select 3,'m1','s13'
union all select 3,'m2','s23'
union all select 3,'m3','s33'--查询
select clerkId as 'Clerk',
max(case when Model='m1' then Spec else '' end) as 'm1',
max(case when Model='m2' then Spec else '' end) as 'm2',
max(case when Model='m3' then Spec else '' end) as 'm3'
from Spec
group by clerkId--结果
/*
Clerk m1 m2 m3
----------- ---- ---- ----
1 s11 s21 s31
2 s12 s22 s32
3 s13 s23 s33(所影响的行数为 3 行)
*/
create table Spec(clerkId int,Model char(2),Spec char(3))
insert into Spec
select 1,'m1','s11'
union all select 1,'m2','s21'
union all select 1,'m3','s31'
union all select 2,'m1','s12'
union all select 2,'m2','s22'
union all select 2,'m3','s32'
union all select 3,'m1','s13'
union all select 3,'m2','s23'
union all select 3,'m3','s33'--动态的
declare @sql varchar(800)
set @sql='select clerkid '
select @sql=@sql+',max(case when Model='''+Model+''' then Spec else null end ) as '+Model
from (select distinct Model from spec) a
set @sql=@sql+' from spec group by clerkid'
Exec(@sql)clerkid m1 m2 m3
----------- ---- ---- ----
1 s11 s21 s31
2 s12 s22 s32
3 s13 s23 s33
警告: 聚合或其他 SET 操作消除了空值。(3 行受影响)