/*范例表
create table 表1 (
號數 char(10),
成績 integer,
科目 char(10) )
insert into 表1 select '1',60,'数学'
union select '1',43,'物理'
union select '1',100,'语文'
union select '2',87,'语文'
union select '2',99,'数学'
union select '2',89,'物理'
union select '2',87,'语文'
*/
Create procedure RowToColumn
@Table varchar(30), --表名
@MasterField varchar(30), --待转名称列名 char字段
@SlaveField varchar(30), --待转数据列名 int型字段
@GroupID varchar(30) --分组ID
as
--调用方法 RowToColumn '表1','科目','成績','號數'
--by jinjazz 环境 SQLServer2000
begin
DECLARE @mSQL VARCHAR(8000)
set @msql = 'DECLARE @SQL VARCHAR(8000)'
set @msql = @msql + ' set @SQL= ''select ' + @GroupID + ''''
set @msql = @msql + ' SELECT @SQL= @SQL+'',max(CASE WHEN ' +
@MasterField + '=''''''+' + @MasterField + '+'''''' then ' + @SlaveField +
' else 0 end )[''+' + @MasterField + '+'']'' from(select distinct ' +
@MasterField + ' from ' + @Table + ') a'
set @msql = @msql + ' SET @SQL=@SQL+ '' from ' + @Table + ' group by ' +
@GroupID + ''''
set @msql = @msql + ' exec(@SQL)'
exec(@msql)
end
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 #
*/