如何将查找出来的数据：
name count
课桌式 100
分组式 360
剧院式 200
董事会 150
U型 100
宴会型 150
变成横向显示：
课桌式 分组式 剧院式 董事会 U型 宴会型
100 360 200 150 100 150
------解决方案--------------------
- SQL code
select max(case name when '课桌式' then [count] else 0 end) as 课桌式, max(case name when '分组式' then [count] else 0 end) as 分组式,....from tb
------解决方案--------------------
- SQL code
create table test([name] varchar(10),[count] int)goinsert testselect '课桌式', 100 union allselect '分组式', 360 union allselect '剧院式', 200 union allselect '董事会', 150 union allselect 'U型', 100 union allselect '宴会型', 150select * from testdeclare @sql varchar(max)select @sql=isnull(@sql+',','')+' max(case when [name]='''+[name]+''' then [count] end) as '''+[name]+''''from(select [name] from test group by [name]) tprint @sqlexec('select [email protected]+' from test' )drop table test/*(6 row(s) affected)name count---------- -----------课桌式 100分组式 360剧院式 200董事会 150U型 100宴会型 150(6 row(s) affected)U型 董事会 分组式 剧院式 课桌式 宴会型----------- ----------- ----------- ----------- ----------- -----------100 150 360 200 100 150(1 row(s) affected)*/
------解决方案--------------------
- SQL code
-- sql server 2000静态select max(case name when '课桌式' then [count] else 0 end) as 课桌式, max(case name when '分组式' then [count] else 0 end) as 分组式, max(case name when '剧院式' then [count] else 0 end) as 剧院式, max(case name when '董事会' then [count] else 0 end) as 董事会, max(case name when 'U型' then [count] else 0 end) as U型 , max(case name when '宴会型'then [count] else 0 end) as 宴会型,from tab--sql server 2000动态[email protected](8000)set @sql='select'select @[email protected]+isnull(@sql+',','')+' max(case when [name]='''+[name]+''' then [count] end) as '''+[name]+''''set @[email protected]+'from (select [name],[count] from tab group by [name]) 'exec(@sql)--sql server 2005静态select * from tab pivot(max(count)for name in(课桌式,分组式,剧院式,董事会,U型,宴会型))--sql server 2005动态[email protected](8000)[email protected]='' [email protected][email protected][email protected]+','+[name] from tab --变量多值赋值[email protected]=stuff(@sql,1,1,'')--去掉首个','[email protected]='select * from tb pivot (max(count) for name in ([email protected]+'))a'exec(@sql)
------解决方案--------------------
- SQL code
select * from (select * from test_three) apivot(max(coun) for name in(分组式,课桌式,剧院式,董事会)) b