交叉表,看了一些例子还是搞不出来!
表:
type_config type_config1 money
1 人民币 20
1 港币 20
2 人民币 50
1 港币 20
2 人民币 20
1 港币 20
2 港币 20
1 人民币 20
输出
type_config1 1 2
人民币 XX1 XX2
港币 XX1 XX2
说明:
XX1为人民币type_config为1的汇总
XX2为港币type_config为2的汇总
type_config1有很多币类型
帮帮我,我搞了一天了..头大..
[解决办法]
--创建测试环境
create table #t(type_config int,type_config1 varchar(20),[money] int)
--插入测试数据
insert #t(type_config,type_config1,[money])
select '1 ', '人民币 ', '20 ' union all
select '1 ', '港币 ', '20 ' union all
select '2 ', '人民币 ', '50 ' union all
select '1 ', '港币 ', '20 ' union all
select '2 ', '人民币 ', '20 ' union all
select '1 ', '港币 ', '20 ' union all
select '2 ', '港币 ', '20 ' union all
select '1 ', '人民币 ', '20 '
--求解过程
declare @sql varchar(8000)
set @sql= 'select type_config1 '
select @sql = @sql + ',sum(case type_config when ' + convert(varchar(20),type_config) + ' then money else 0 end) as [ ' + convert(varchar(20),type_config) + '] '
from (select distinct type_config from #t) _x
select @sql = @sql + ' from #t group by type_config1 '
exec( @sql)
--删除测试环境
drop table #t
/*--测试结果
type_config1 1 2
-------------------- ----------- -----------
港币 60 20
人民币 40 70
*/
[解决办法]
create table t
(type_config int,
type_config1 varchar(20),
[money] money
)
insert into t
select 1 , '人民币 ', 20
union all select 1 , '港币 ', 20
union all select 2 , '人民币 ', 50
union all select 1 , '港币 ', 20
union all select 2 , '人民币 ', 20
union all select 1 , '港币 ', 20
union all select 2 , '港币 ', 20
union all select 1 , '人民币 ', 20
select * from t
select type_config1,sum(case type_config when 1 then [money] else 0 end) as '1 ',
sum(case type_config when 2 then [money] else 0 end) as '2 ' from t group by type_config1
drop table t
[解决办法]
我写的一个简单的交叉制表存储过程：
--交叉制表(Myself)
CREATE PROCEDURE sp_makeCrosstab (
@tablename as varchar(50),
@namefield as varchar(50),
@typefield as varchar(50),
--@typefield 所取的字段的数据不能为数字型，否则出错，
--可以将最后一句 N ' else 0 end) as ' + rtrim(@Temp) 改为N ' else 0 end) as A ' + rtrim(@Temp)，即加一个固定的字符。
@valuefield as varchar(50)
)
AS
DECLARE @Temp varchar(20)
DECLARE @SqlString varchar(500)
DECLARE @strCursor varchar(500)
SET @SqlString = N 'select ' + @namefield
--动态建立游标
SET @strCursor = 'DECLARE cuTemp CURSOR FOR SELECT DISTINCT '
+ @typefield + ' FROM ' + @tablename
EXEC (@strCursor)
OPEN cuTemp
FETCH NEXT FROM cuTemp into @Temp
WHILE @@Fetch_Status = 0
BEGIN
SET @Sqlstring = @SqlString + N ',SUM(case when ' + @typefield + N '= ' ' '+ rtrim(@Temp)
+ N ' ' ' then ' + @valuefield + N ' else 0 end) as ' + rtrim(@Temp)
FETCH NEXT FROM cuTemp into @Temp
END
CLOSE cuTemp
DEALLOCATE cuTemp
SET @Sqlstring = @SqlString + N ' FROM ' + @tablename + N ' GROUP BY ' + @namefield
EXEC (@Sqlstring)
调用：
exec sp_makeCrosstab '成绩单 ', '学生编号 ', '科目 ', '成绩 '
希望对你有帮助。
