34,594
社区成员
发帖
与我相关
我的任务
分享
create table tb(name varchar(5),color varchar(5),cust varchar(5), jiage float)
insert into tb
select '产品A','黄色','客户1',0.1 union
select '产品A','红色','客户2',0.1 union
select '产品A','绿色','客户2',0.2 union
select '产品A','绿色','客户2',0.3 union
select '产品B','黄色','客户3',0.2 union
select '产品B','绿色','客户2',0.2
declare @sql varchar(8000)
set @sql = 'select name '
select @sql = @sql + ' , min(case when color=''' + color+''' and cust='''+
cust + ''' then jiage else null end) [' + color+cust + ']'
from tb group by color,cust
set @sql = @sql + ' from tb group by name'
exec(@sql)
create table tb(name varchar(5),color varchar(5),cust varchar(5), jiage float)
insert into tb
select '产品A','黄色','客户1',0.1 union
select '产品A','红色','客户2',0.1 union
select '产品A','绿色','客户2',0.2 union
select '产品A','绿色','客户2',0.3 union
select '产品B','黄色','客户3',0.2 union
select '产品B','绿色','客户2',0.2
go
declare @sql varchar(8000)
set @sql = 'select name '
select @sql = @sql + ',min(case when color=''' + color+''' and cust='''+ cust
+ ''' then jiage else null end) [' + color+cust + ']'
from (select distinct color,cust from tb)t
order by color,cust
set @sql = @sql + ' from tb group by name'
exec(@sql)
drop table tb
/***************
name 红色客户2 黄色客户1 黄色客户3 绿色客户2
----- ---------------------- ---------------------- ---------------------- ----------------------
产品A 0.1 0.1 NULL 0.2
产品B NULL NULL 0.2 0.2
警告: 聚合或其他 SET 操作消除了空值。
(2 行受影响)
declare @t table
(名称 varchar(5),颜色 varchar(4),客户 varchar(5),价格 numeric(2,1))
insert into @t
select '产品A','黄色','客户1',0.1 union all
select '产品A','红色','客户2',0.1 union all
select '产品A','绿色','客户2',0.2 union all
select '产品A','绿色','客户2',0.3 union all
select '产品B','黄色','客户3',0.2 union all
select '产品B','绿色','客户2',0.2
;with maco as(
select 名称,颜色,客户,价格=min(价格) from @t
group by 名称,颜色,客户
)
select 名称,
left(max(case when 颜色='黄色' then 客户+'('+ltrim(价格)+')' end),10) as '黄色',
left(max(case when 颜色='红色' then 客户+'('+ltrim(价格)+')' end),10) as '红色',
left(max(case when 颜色='绿色' then 客户+'('+ltrim(价格)+')' end),10) as '绿色'
from maco group by 名称
/*
名称 黄色 红色 绿色
----- -------------------- -------------------- --------------------
产品A 客户1(0.1) 客户2(0.1) 客户2(0.2)
产品B 客户3(0.2) NULL 客户2(0.2)
*/
--我暂时只能弄成这种样子...
--这个貌似应该在程序中处理
create table tb(name varchar(5),color varchar(5),cust varchar(5), jiage float)
insert into tb
select '产品A','黄色','客户1',0.1 union
select '产品A','红色','客户2',0.1 union
select '产品A','绿色','客户2',0.2 union
select '产品A','绿色','客户2',0.3 union
select '产品B','黄色','客户3',0.2 union
select '产品B','绿色','客户2',0.2
declare @sql varchar(8000)
set @sql = 'select name '
select @sql = @sql + ' , min(case when color=''' + color+''' and cust='''+
cust + ''' then jiage else null end) [' + color+cust + ']'
from tb group by color,cust
set @sql = @sql + ' from tb group by name'
exec(@sql)
/*
name 红色客户2 黄色客户1 黄色客户3 绿色客户2
----- ---------------------- ---------------------- ---------------------- ----------------------
产品A 0.1 0.1 NULL 0.2
产品B NULL NULL 0.2 0.2
警告: 聚合或其他 SET 操作消除了 Null 值。
(2 行受影响)