行转列的老问题

[btlyeo]

表结构：
店面 产品 金额
--------------------------
A店 产品1 100
A店 产品2 100
A店 产品3 100
B店 产品1 200
B店 产品3 200
……
---------------------------
要求的结果是：
店面名称 产品1 产品2 产品3 …… 总计
A 100 100 100 …… 300
B 200 0 200 …… 400

这里产品数量不是固定的，店面也不是固定的
-----------------------------------
数据表里大约30万+的数据，
要怎么写这个存储过程能够使效率更高一些？

[子陌红尘]

declare @sql varchar(8000)
set @sql='select 店面'
select @sql=@sql+','+产品+'=sum(case 产品 when '''+产品+''' then 金额 else 0 end)'
from 表 group by 产品 order by 产品
set @sql=@sql+',总计=sum(金额) from 表 group by 店面'
exec(@sql)

[IFocusYou]

楼上的有些问题.

[dawugui]

行列转换加合计

例如有表A某些人每月消费数据如下：
id data month
001 11 1
001 12 2
001 13 3
001 14 4
001 15 5
001 16 6
001 17 7
001 18 8
001 19 9
001 110 10
001 111 11
001 112 12
002 21 1
002 22 2
002 23 3
002 24 4
002 25 5
002 26 6
002 27 7
002 28 8
002 29 9
002 210 10
002 211 11
002 212 12
要实现如下结果：





第一个：

SELECT id as '人员' ,
SUM(CASE month WHEN 1 THEN data ELSE 0 END) AS '1月' ,
SUM(CASE month WHEN 2 THEN data ELSE 0 END) AS '2月' ,
SUM(CASE month WHEN 3 THEN data ELSE 0 END) AS '3月' ,
SUM(CASE month WHEN 4 THEN data ELSE 0 END) AS '4月' ,
SUM(CASE month WHEN 5 THEN data ELSE 0 END) AS '5月' ,
SUM(CASE month WHEN 6 THEN data ELSE 0 END) AS '6月' ,
SUM(CASE month WHEN 7 THEN data ELSE 0 END) AS '7月' ,
SUM(CASE month WHEN 8 THEN data ELSE 0 END) AS '8月' ,
SUM(CASE month WHEN 9 THEN data ELSE 0 END) AS '9月' ,
SUM(CASE month WHEN 10 THEN data ELSE 0 END) AS '10月' ,
SUM(CASE month WHEN 11 THEN data ELSE 0 END) AS '11月' ,
SUM(CASE month WHEN 12 THEN data ELSE 0 END) AS '12月'
FROM a
GROUP BY ID

第二个：

SELECT id as '人员' ,
SUM(CASE month WHEN 1 THEN data ELSE 0 END) AS '1月' ,
SUM(CASE month WHEN 2 THEN data ELSE 0 END) AS '2月' ,
SUM(CASE month WHEN 3 THEN data ELSE 0 END) AS '3月' ,
SUM(CASE month WHEN 4 THEN data ELSE 0 END) AS '4月' ,
SUM(CASE month WHEN 5 THEN data ELSE 0 END) AS '5月' ,
SUM(CASE month WHEN 6 THEN data ELSE 0 END) AS '6月' ,
SUM(CASE month WHEN 7 THEN data ELSE 0 END) AS '7月' ,
SUM(CASE month WHEN 8 THEN data ELSE 0 END) AS '8月' ,
SUM(CASE month WHEN 9 THEN data ELSE 0 END) AS '9月' ,
SUM(CASE month WHEN 10 THEN data ELSE 0 END) AS '10月' ,
SUM(CASE month WHEN 11 THEN data ELSE 0 END) AS '11月' ,
SUM(CASE month WHEN 12 THEN data ELSE 0 END) AS '12月' ,
SUM(data) as '合计'
FROM a
GROUP BY ID

[btlyeo]

你的好象是时间是固定的，要知道我说的产品是动态的，不确定有多少呢，也是一个表里的数据

[子陌红尘]

如果SQL字符串不会超过8000字节:
-------------------------------------------------------------------------
create table test(店面 varchar(10),产品 varchar(10),金额 int)
insert into test select 'A店','产品1',100
insert into test select 'A店','产品2',100
insert into test select 'A店','产品3',100
insert into test select 'B店','产品1',200
insert into test select 'B店','产品3',200
go

declare @sql varchar(8000)
set @sql='select 店面'
select @sql=@sql+','+产品+'=sum(case 产品 when '''+产品+''' then 金额 else 0 end)'
from test group by 产品 order by 产品
set @sql=@sql+',总计=sum(金额) from test group by 店面'
exec(@sql)
go

/*
店面 产品1 产品2 产品3 总计
---------- ----------- ----------- ----------- -----------
A店 100 100 100 300
B店 200 0 200 400
*/

drop table test
go

[中国风]

libin_ftsafe(子陌红尘：当libin告别ftsafe)正解!楼主可以结贴了

[btlyeo]

我把子陌的表结构的字符型换成int类型就错了
create table test(店面 varchar(10),产品 int,金额 int)
insert into test select 'A店','1',100
insert into test select 'A店','2',100
insert into test select 'A店','3',100
insert into test select 'B店','1',200
insert into test select 'B店','3',200
这样就报错了~

[btlyeo]

将 varchar 值 '=sum(case 产品 when '' 转换为数据类型为 int 的列时发生语法错误。

[子陌红尘]

我把子陌的表结构的字符型换成int类型就错了
------------------------------------------------------------------------------------
create table test(店面 varchar(10),产品 int,金额 int)
insert into test select 'A店','1',100
insert into test select 'A店','2',100
insert into test select 'A店','3',100
insert into test select 'B店','1',200
insert into test select 'B店','3',200
go

declare @sql varchar(8000)
set @sql='select 店面'
select @sql=@sql+',['+rtrim(产品)+']=sum(case 产品 when '''+rtrim(产品)+''' then 金额 else 0 end)'
from test group by 产品 order by 产品
set @sql=@sql+',总计=sum(金额) from test group by 店面'
exec(@sql)
go

[子陌红尘]

很简单，做一下类型转化。

[btlyeo]

多谢！马上结帖

[ych251314]

学习中，类型应该怎么转换呢？

[ych251314]

在哪里转呢？