CJ表
张三 语文 80
张三 数学 100
张三 物理 79
JIM 物理 100
MACK DJ 100
MACK NULL NULL存储过程set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER PROCEDURE [dbo].[pro_ROWTOCOL]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON; -- Insert statements for procedure here
declare @sql varchar(4000)
set @sql = 'select Name'
select @sql = @sql + ',isnull(sum(case Subject when '''+Subject+''' then Result end),0) ['+Subject+']'
from (select distinct Subject from CJ) as a
select @sql = @sql+' from cj group by name'
exec(@sql)END
张三 语文 80
张三 数学 100
张三 物理 79
JIM 物理 100
MACK DJ 100
MACK NULL NULL存储过程set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER PROCEDURE [dbo].[pro_ROWTOCOL]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON; -- Insert statements for procedure here
declare @sql varchar(4000)
set @sql = 'select Name'
select @sql = @sql + ',isnull(sum(case Subject when '''+Subject+''' then Result end),0) ['+Subject+']'
from (select distinct Subject from CJ) as a
select @sql = @sql+' from cj group by name'
exec(@sql)END
既然连课程都没有还有统计的价值吗?直接 where 课程 is not null
=>from (select Subject from CJ where Subject is not null group by Subject ) as a
这个是对转换过的单元格进行赋值把。
单元格没有的,我已经赋值为零了。
现在是有数据为NULL,等于列名为NULL 所以无法正常显示
先取消
set QUOTED_IDENTIFIER ON
goALTER PROCEDURE [dbo].[pro_ROWTOCOL]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON; -- Insert statements for procedure here
declare @sql varchar(4000)
set @sql = 'select Name'
select @sql = @sql + ',isnull(sum(case Subject when '''+Subject+''' then Result end),0) ['+Subject+']'
--我在这里改了,这样当有NULL时就会出现‘未知’列。
from (select ISNULL(Subject,'未知')AS Subject from CJ GROUP BY Subject) as a
select @sql = @sql+' from cj group by name'
exec(@sql)END没用过SQL的存储过程,自己改了下。空值问题解决了,但是单元格中如果是 ''(空字符串,就是string.Empty)。
吐血...期待高手
额,哪行太长,忘记写了一句。
是单元格中如果是''(空字符串,就是string.Empty),会导致无法返回查询结果。我想原因可能和 null类似,就是 行转列的时候会有一个列名为空的列。我现在是 WHERE 把 值为‘’全都排除掉了。但是原程序不是我写的,不知道这样处理会不会产生数据丢失,所以最好能把为''的值 也统计进来
你在拼sql 的时候 把'' 当做一种 case 来处理max(case when Subject='''+Subject+''' then Result when Subject='' then '空' end) as ['+Subject+']'
这个我试过,没用的。 我是要统计和,也不是最大值。在网上搜到用 “SELECT CASE”替换语句进行替换解决了。