table1
id kind value
1 car 3
1 food 3
2 food 4
2 bike 5
3 car 4
.
.
.
变为 table2
id food bike car ...
1 3 null 1
2 4 5 null...
3 null null 4并且table1的kind字段可以动态添加的
高手帮忙,我找些资料也没搞出来,呵呵
id kind value
1 car 3
1 food 3
2 food 4
2 bike 5
3 car 4
.
.
.
变为 table2
id food bike car ...
1 3 null 1
2 4 5 null...
3 null null 4并且table1的kind字段可以动态添加的
高手帮忙,我找些资料也没搞出来,呵呵
select @sql=''
select @sql=@sql+',max(case when kine='''+kind+''' then value else null end) as ['+kind+']' from table1 group by kind
select @sql='select id'+@sql+' from table1 group by id'
exec (@sql)--写滥掉的东西,都有点怀疑你是不是找过资料了
Declare @S Varchar(1000)
Set @S=''
Select @S=@S+',SUM(Case kind When '''+kind+''' Then value Else 0 End) As '+kind From (Select Distinct kind From TEST) A Order By kind
Set @S='Select id '+@S+' From TEST Group By id Order By id '
EXEC(@S)
Declare @S Varchar(8000)
Set @S=''
Select @S=@S+',Max(Case kind When '''+kind+''' Then value Else null End) As '+kind
From table1 Group By kind Order By kind
Select @S='Select id'+@S+' From table1 Group By id Order By id'
EXEC(@S)
pivot
(sum(value)
for kind in ([food],[bike],[car])
) as pivt
都是向paoluo(一天到晚游泳的鱼)学到的东西!