有表test,结果如下:
type state value
1 01 100
2 01 200
3 01 300
1 02 400
2 02 500
3 02 600
1 03 700
2 03 800
3 03 900需要求出以下结果:
以state分组,type为列头,state type1 type2 type3
01 100 200 300
02 400 500 600
03 700 800 900请问sql语句怎么写?
type state value
1 01 100
2 01 200
3 01 300
1 02 400
2 02 500
3 02 600
1 03 700
2 03 800
3 03 900需要求出以下结果:
以state分组,type为列头,state type1 type2 type3
01 100 200 300
02 400 500 600
03 700 800 900请问sql语句怎么写?
from table
gropu by state
from test
group by state
呵呵,我写的和一楼的除了在TYPE类型上有所不同外,其它一样,如果TYPE是VARCHAR就用'1',如果是NUMBER,就直接用1就行了
其实都可以用的,只不过如果是VARCHAR类型的话,加上''严谨点
这种问题比较讨厌,也老有人问,典型的行列转换加不固定的行列
insert into test1 select 1,'01',100
union all select 2,'01',200
union all select 3,'01',300
union all select 1,'02',400
union all select 2,'02',500
union all select 3,'02',600
union all select 1,'03',700
union all select 2,'03',800
union all select 3,'03',900declare @sql varchar(1000)
set @sql='select state'
select @sql=@sql+',[type'+cast(type as varchar)+']=max(case type when '''+ cast(type as varchar) +''' then value else 0 end)'
from (select distinct type from test1)a
set @sql=@sql+' from test1 group by state'
exec(@sql)
insert into test1 select 1,'01',100
union all select 2,'01',200
union all select 3,'01',300
union all select 1,'02',400
union all select 2,'02',500
union all select 3,'02',600
union all select 1,'03',700
union all select 2,'03',800
union all select 3,'03',900 declare @sql varchar(1000)
set @sql='select state'
select @sql=@sql+',[type'+cast(type as varchar)+']=max(case type when '''+ cast(type as varchar) +''' then value else 0 end)'
from (select distinct type from test1)a
set @sql=@sql+' from test1 group by state'
exec(@sql)