表:F1 F2 F3 Prid A 食品 x M1001
B 123 y M1001
C 药品 h M1001
D 其它 z M1001
---结果
prid A B C D
M1001 食品 123 药品 其它
B 123 y M1001
C 药品 h M1001
D 其它 z M1001
---结果
prid A B C D
M1001 食品 123 药品 其它
set @sql=''
select @sql=@sql+',['+F1+']=max(case F1 when '''+F1+''' then F2 end)' from 表 group by F1 order by F1
set @sql='select Prid'+@sql+' from 表 group by Prid'
exec(@sql)
insert into # select 'A','食品','x','M1001'
insert into # select 'B','123 ','y','M1001'
insert into # select 'C','药品','h','M1001'
insert into # select 'D','其它','z','M1001'
go
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',['+F1+']=max(case F1 when '''+F1+''' then F2 end)' from # group by F1 order by F1
set @sql='select Prid'+@sql+' from # group by Prid'
exec(@sql)
---结果
/*
Prid A B C D
---------- ---------- ---------- ---------- ----------
M1001 食品 123 药品 其它
*/
select 'A' as F1, '食品' as F2, 'x' as F3, 'M1001' as Prid
into test
union select 'B', '123', 'y', 'M1001'
union select 'C', '药品', 'h', 'M1001'
union select 'D', '其它', 'z', 'M1001'
----------------------------------------------
declare @s varchar(2000)
set @s = 'select prid'
select @s = @s + ', min(case when F1 = ''' + F1 + ''' then F2 end) as [' + F1 + ']'
from (select distinct F1 from test) a
set @s = @s + ' from test group by Prid'
exec(@s)
/*
prid A B C D
M1001 食品 123 药品 其它*/----------------------------------------------
drop table test
1.包含两个表------典型行列转换问题例子
--建立测试环境
create table tb1 (id nvarchar(10),type nvarchar(10))
insert into tb1 select '11','a' union all select '22','b' union all select '33','c'
create table tb2 (n int,type nvarchar(10),num int)
insert into tb2 select '1','11','4' union all select '1','11','5'
union all select '2','22','8' union all select '3','22','5'
--查询处理
DECLARE @SQL VARCHAR(8000)
SET @SQL='select n '
SELECT @SQL= @SQL+',sum(case when type='+ttt+' then num else 0 end)['+tt+']' from
(select distinct a.type as tt,isnull(b.type,'0') as ttt from tb2 b right join tb1 a on a.id=b.type) b
set @sql=@sql+' from tb2 group by n'
print @sql
exec(@sql)
go--删除测试环境
Drop Table tb1,tb2
--这可以给你启发。。
from 表 where Prid = 'M1001'
-------------------------------------------------------------------
我的SQL里对F2的处理是取最大值,有特殊的要求?
set @sql=''
select @sql=@sql+',['+F1+']=max(case F1 when '''+F1+''' then F2 end)' from 表 group by F1 order by F1
set @sql='select Prid'+@sql+' from 表 group by Prid'
exec(@sql)