我有一张表，分组统计后，如何将结果用一行显示出来
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],[email protected]+' from [batpics] group by [picture_id]'exec(@sql)