SQL 分组 行变列的一个例子
(SQL SERVER 2000 測試OK)
1. 表格A原始数据如下:
CREATE TABLE [dbo].[A] (
[C1] [varchar] (50) COLLATE Chinese_PRC_CS_AS NULL ,
[C2] [varchar] (50) COLLATE Chinese_PRC_CS_AS NULL ,
[C3] [varchar] (50) COLLATE Chinese_PRC_CS_AS NULL ,
[C4] [varchar] (50) COLLATE Chinese_PRC_CS_AS NULL
)
insert into A ('95533','SZ','44','123000')
insert into A ('95566','SZ','44','233300')
insert into A ('95588','GZ','44','4566')
insert into A ('95599','GZ','44','456666')
insert into A ('95533','ZH','44','333333')
insert into A ('95577','DG','44','555555')
insert into A ('95588','ST','44','44444')
2. 分组行变列 处理数据:
DECLARE @SQL VARCHAR(4000)
SET @SQL='SELECT C2'
SELECT @SQL= @SQL+ ',max(CASE WHEN C1 = ''' + C1 + ''' THEN C4 ELSE 0 END) ['+C1+']' FROM (SELECT DISTINCT C1 FROM A) TAB
SET @SQL=@SQL+ ' FROM A GROUP BY C2'
EXEC (@SQL)
得到如下结果：
C2 95533 95566 95577 95588 95599
--------------------------------------------
DG 0 0 555555 0 0
GZ 0 0 0 4566 456666
ST 0 0 0 44444 0
SZ 123000 233300 0 0 0
ZH 333333 0 0 0 0
OK, 分组就这样完成了.
参考经典实例：
/* 实例一
create table t (id int identity,name varchar(10),code int)
insert t values('人口',20)
insert t values('经济',12)
insert t values('文化',15)
insert t values('土地',45)
declare @sql varchar(1000)
set @sql = ''
select @sql = @sql+name+'=max(case when name='''+name+''' then code else null end),' from t
--print @sql
set @sql = left(@sql,len(@sql) - 1)
set @sql = 'select [姓名]=''年龄'', '+@sql+' from t'
exec (@sql)
--drop table t
实例二
create table #(a varchar(100),b int)
insert # values('aa',11)
insert # values('bb',1)
insert # values('aa',45)
insert # values('cc',81)
insert # values('a',11)
insert # values('aay',561)
insert # values('a',14)
declare @sql varchar(8000)
set @sql = 'select '
select @sql = @sql + 'sum(case a when '''+a+'''
then b else 0 end) '+a+'的数量,'
from (select distinct a from #) as a
select @sql = left(@sql,len(@sql)-1) + ' from #'
exec(@sql)
-- drop table #
*/