分组统计后怎么将结果显示于一行

分组统计后如何将结果显示于一行
我有一张表，分组统计后，如何将结果用一行显示出来

CREATE TABLE [dbo].[batpics](
[picture_id] [char](11) NOT NULL,
[file_type] [varchar](20) NOT NULL
)

insert into batpics (picture_id,file_type) values ('P0000000000','3')
insert into batpics (picture_id,file_type) values ('P0000000000','3')
insert into batpics (picture_id,file_type) values ('P0000000000','3')
insert into batpics (picture_id,file_type) values ('P0000000000','3')
insert into batpics (picture_id,file_type) values ('P0000000000','3')
insert into batpics (picture_id,file_type) values ('P0000000000','3')
insert into batpics (picture_id,file_type) values ('P0000000000','2')
insert into batpics (picture_id,file_type) values ('P0000000000','2')
insert into batpics (picture_id,file_type) values ('P0000000000','2')
insert into batpics (picture_id,file_type) values ('P0000000000','2')
insert into batpics (picture_id,file_type) values ('P0000000000','2')
insert into batpics (picture_id,file_type) values ('P0000000000','2')
insert into batpics (picture_id,file_type) values ('P0000000000','2')
insert into batpics (picture_id,file_type) values ('P0000000000','2')

我想要的结果是
picture_id file_type count（file_type） file_type count（file_type）
P0000000000 2 8 3 6

[解决办法]

SQL code

CREATE TABLE [dbo].[batpics]([picture_id] [char](11) NOT NULL,[file_type] [varchar](20) NOT NULL)insert into batpics (picture_id,file_type) values ('P0000000000','3')insert into batpics (picture_id,file_type) values ('P0000000000','3')insert into batpics (picture_id,file_type) values ('P0000000000','3')insert into batpics (picture_id,file_type) values ('P0000000000','3')insert into batpics (picture_id,file_type) values ('P0000000000','3')insert into batpics (picture_id,file_type) values ('P0000000000','3')insert into batpics (picture_id,file_type) values ('P0000000000','2')insert into batpics (picture_id,file_type) values ('P0000000000','2')insert into batpics (picture_id,file_type) values ('P0000000000','2')insert into batpics (picture_id,file_type) values ('P0000000000','2')insert into batpics (picture_id,file_type) values ('P0000000000','2')insert into batpics (picture_id,file_type) values ('P0000000000','2')insert into batpics (picture_id,file_type) values ('P0000000000','2')insert into batpics (picture_id,file_type) values ('P0000000000','2')CREATE VIEW V_BatpicsASSELECT picture_id, file_type, COUNT(1) AS ICOUNTFROM batpicsGROUP BY picture_id, file_typeDeclare @SQL varchar(8000)Set @sql = 'Select picture_id 'Select @sql = @sql + ' , Max(Case file_type When ''' + file_type + ''' Then ICOUNT Else 0 End) [' + 'file_type' + file_type + ']' From (Select Distinct file_type From V_Batpics) As ASet @SQL = @SQL + ' From V_Batpics Group By picture_id'Exec(@SQL) DROP VIEW V_batpics
[解决办法]
SQL code
declare @sql varchar(max)select @sql=isnull(@sql+',','')+''''+t.[file_type]+''' file_type, case when [picture_id]='''+t.[picture_id]+''' then '+convert(varchar(5),t.[cnt])+' end ''count('+t.[file_type]+')'''from (    select [picture_id],[file_type],count([file_type]) cnt from [batpics] group by [picture_id],[file_type])tprint @sqlselect @sql='select [picture_id],'+@sql+' from [batpics] group by [picture_id]'exec(@sql)