我有一张表，分组统计后，如何将结果用一行显示出来
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
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_Batpics
AS
SELECT picture_id, file_type, COUNT(1) AS ICOUNT
FROM batpics
GROUP BY picture_id, file_type
Declare @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 A
Set @SQL = @SQL + ' From V_Batpics Group By picture_id'
Exec(@SQL)
DROP VIEW V_batpics
\
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]
)t
print @sql
select @sql='select [picture_id],'+@sql+' from [batpics] group by [picture_id]'
exec(@sql)