sql 行轉列

create table t(company varchar(30),goods varchar(30),value int)

insert into t

select 'compal','compute',500  union all

select 'compal','CPU',400  union all

select 'compal','mouse',200  union all

select 'FOX','compute',450  union all

select 'FOX','mouse',100  union all

select 'FOX','LCT',350  union all

select 'AB','compute',600  union all

select 'AB','CPU',100 

 

 

declare @sql varchar(8000)

set @sql=''

select @sql=@sql+',max(case when company='''+company+''' then value else 0 end) as '+company from t group by company

select @sql='select goods '+@sql+' from t group by goods'

exec (@sql)

drop t

print @sql

max(case when company='AB' then value else 0 end) as AB,
max(case when company='compal' then value else 0 end) as compal,
max(case when company='FOX' then value else 0 end) as FOX

 

drop table t