日期:2014-05-17  浏览次数:20808 次

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

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_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

------解决方案--------------------
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]
)t
print @sql
select @sql='select [picture_id],'+@sql+' from [batpics] group by [picture_id]'
exec(@sql)