Create Table T
(
类别 Varchar(10),
商品 Varchar(10),
指标 Varchar(10),
指标值 Varchar(10)
)
insert into T values('蔬菜','胡萝卜','产地','河北')
insert into T values('蔬菜','胡萝卜','价格','1')
insert into T values('蔬菜','胡萝卜','保质期','10天')
insert into T values('蔬菜','胡萝卜','清洗','True')
insert into T values('蔬菜','黄瓜','产地','天津')
insert into T values('蔬菜','黄瓜','价格','1.5')
insert into T values('蔬菜','黄瓜','保质期','7天')
insert into T values('蔬菜','黄瓜','有机','True')
select * from T
pivot
(
max(指标值)
For
指标 in(产地,价格,保质期,清洗,有机)
)p
--如果指标项目不固定就采用动态拼接的sql
Declare @W Varchar(7000)=''
Declare @sql Varchar(8000)=''
Select @W=@W+','+指标 From (
Select distinct 指标 From T
) S
Set @sql='
select * from T
pivot
(
max(指标值)
For
指标 in('+STUFF(@W,1,1,'')+')
)p'
Exec (@sql)
