统计,将行变成列的SQL语句?

[cqcbmfc]

col1 col2 col3
1 名字1 a
2 名字1 a
3 名字1 b
4 名字1 c

查询结果:
col2 a b c
名字1 2 1 1

[了缘]

比较复杂，学习，

[cqcbmfc]

难道就没有高手知道吗?

[lcq9732]

select col2,(select Count(*) from Table where col3 ='a') as a,
,(select Count(*) from T where col3 ='b') as b,
,(select Count(*) from T where col3 ='c') as c
from Table group by col2

[了缘]

select col2 ,col3,count(col2) from table
然后用游标试试，看可不可以，

[lcq9732]

多了几个逗号，自己删除了

[cqcbmfc]

col3 的值是动态产生的怎么办呢?

[zonelive]

别人的，参考一下吧：
--测试

--测试数据
create table 表(f_bi varchar(20),f_bn varchar(20),f_bv float)
insert 表 select 'a','参数1',3.56
union all select 'a','参数2',1.52
union all select 'a','参数3',7.13
union all select 'b','参数1',9.41
union all select 'b','参数2',2.61
union all select 'b','参数4',8.03
union all select 'c','参数1',9.56
union all select 'c','参数2',3.76
union all select 'c','参数4',3.54
union all select 'd','参数1',0.85
union all select 'd','参数2',2.13
union all select 'd','参数3',1.23
union all select 'e','参数5',65.4
union all select 'e','参数6',67.6
go

--通用的存储过程
create proc p_qry
@参数 varchar(1000),
@行 varchar(8000)
as
declare @s varchar(8000),@i int
select @参数=@参数+',',@行=''''+replace(@行,',',''',''')+''''
,@i=charindex(',',@参数),@s=''
while @i>0
begin
select @s=@s+','+left(@参数,@i-1)
+'=cast(sum(case f_bn when '''
+left(@参数,@i-1)+''' then f_bv end) as decimal(10,2))'
,@参数=substring(@参数,@i+1,1000)
,@i=charindex(',',@参数)
end

exec('select f_bi'+@s+'
from 表
where f_bi in('+@行+')
group by f_bi
')
go

exec p_qry '参数1,参数2,参数3,参数4','a,b,c,d'
exec p_qry '参数1,参数5','a,e'
go

--删除测试
drop table 表
drop proc p_qry

/*--测试结果
f_bi 参数1 参数2 参数3 参数4
-------------------- ------------ ------------ ------------ ------------
a 3.56 1.52 7.13 NULL
b 9.41 2.61 NULL 8.03
c 9.56 3.76 NULL 3.54
d .85 2.13 1.23 NULL

（所影响的行数为 4 行）

f_bi 参数1 参数1
-------------------- ------------ ------------
a 3.56 NULL
e NULL 65.40

（所影响的行数为 2 行）
--*/

[sangshuyezi]

Select * into tmpABC from (Select 1 as col1,N'名字1' as col2, 'a' as col3
union select 2,N'名字1','a'
union select 3,N'名字1','b'
union select 4,N'名字1','c') a

declare @StrCol3 nvarchar(1000)
declare @StrSql nvarchar(1000)
declare @Col2 nvarchar(10)
declare @Col3 nvarchar(10)
declare @Counts nvarchar(10)
set @StrCol3=''
set @StrSql=''
declare cursor_out cursor local for Select col2,col3,count(*) as Counts from tmpABC group by col2,col3
open cursor_out
fetch next from cursor_out into @Col2,@Col3,@Counts
while @@fetch_status=0
begin
set @StrCol3=@StrCol3 + ' ,(Select count(*) from tmpABC where Col3= ''' + @Col3 + ''' and Col2=N''' + @Col2 +''') as ' + @Col3
fetch next from cursor_out into @Col2,@Col3,@Counts
end
CLOSE cursor_out
DEALLOCATE cursor_out
set @StrSql='Select Distinct col2' + @StrCol3 + ' from tmpABC'
exec(@StrSql)

drop table tmpABC
呵呵，那些很简单，但是最后一句话（exec(@StrSql)想了我五分钟。