将null 换成0

[好帅的一条鱼]

下面是写的行转列的语句，如何将指为NULL的用0替换掉，谢谢

    declare @sql varchar(8000)
    select @sql=isnull(@sql+'],[' , '')  + fdbs from daysphz  group by fdbs
    set @sql='['+@sql+']'
    exec ('select * from (select spid,shl,fdbs from daysphz ) a  pivot (sum(shl) for fdbs in ('+@sql+'))b 
           order by spid')
------------------
SPH00000001	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
SPH00000002	11.00	NULL	NULL	NULL	NULL	NULL	NULL	NULL	9.00
SPH00000003	1.00	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
SPH00000004	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
SPH00000005	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL

--------------------

[唐诗三百首]

try this,

declare @sql varchar(8000),@sql2 varchar(8000)

select @sql=isnull(@sql+',','')+'['+fdbs+']'
  from daysphz group by fdbs
  
select @sql2=isnull(@sql2+',','')+'isnull(['+fdbs+'],'''') '''+fdbs+''' '
  from daysphz group by fdbs 

exec('select spid,'+@sql2+' from (select spid,shl,fdbs from daysphz) a pivot(sum(shl) for fdbs in ('+@sql+')) b 
       order by spid')

[-Tracy-McGrady-]

在这些上面加isnull(col,0)试试， spid,shl,fdbs

[ai_li7758521]

    declare @sql varchar(8000),@col varchar(8000)

    SELECT 
		@sql=isnull(@sql+'],[' , '')  + fdbs, 
		@col=ISNULL(@col,'')+',ISNULL('+fdbs+',0) as '+fdbs
	from daysphz  
	group by fdbs

    set @sql='['+@sql+']'


    exec ('select spid'+@col+' from (select spid,shl,fdbs from daysphz ) a  pivot (sum(shl) for fdbs in ('+@sql+'))b order by spid')

[好帅的一条鱼]

引用 2 楼 ai_li7758521 的回复:

    declare @sql varchar(8000),@col varchar(8000)

    SELECT 
		@sql=isnull(@sql+'],[' , '')  + fdbs, 
		@col=ISNULL(@col,'')+',ISNULL('+fdbs+',0) as '+fdbs
	from daysphz  
	group by fdbs

    set @sql='['+@sql+']'


    exec ('select spid'+@col+' from (select spid,shl,fdbs from daysphz ) a  pivot (sum(shl) for fdbs in ('+@sql+'))b order by spid')

有点报错

[-Tracy-McGrady-]

declare @sql varchar(8000)
    select @sql=isnull(@sql+'],[' , '')  + fdbs from daysphz  group by fdbs
    set @sql='['+@sql+']'
    exec ('select * from (select isnull(spid,0),isnull(shl,0),isnull(fdbs,0) from daysphz ) a  pivot (sum(shl) for fdbs in ('+@sql+'))b 
           order by spid')

[好帅的一条鱼]

引用 4 楼 yangsh0722 的回复:

declare @sql varchar(8000)
    select @sql=isnull(@sql+'],[' , '')  + fdbs from daysphz  group by fdbs
    set @sql='['+@sql+']'
    exec ('select * from (select isnull(spid,0),isnull(shl,0),isnull(fdbs,0) from daysphz ) a  pivot (sum(shl) for fdbs in ('+@sql+'))b 
           order by spid')

船长，报错更多了

[LongRui888]

shl字段加了isnull函数，改成这样：

    declare @sql varchar(8000)
    select @sql=isnull(@sql+'],[' , '')  + fdbs from daysphz  group by fdbs
    set @sql='['+@sql+']'
    exec ('select * from (select spid,isnull(shl,0),fdbs from daysphz ) a  pivot (sum(shl) for fdbs in ('+@sql+'))b 
           order by spid')

[-Tracy-McGrady-]

select * from (sele 这里不要用*一个一个的isnull(col,0)

[-Tracy-McGrady-]

http://bbs.csdn.net/topics/90155176 给你搜了个

[好帅的一条鱼]

这里面的列是动态的，不确定的呢，

引用 6 楼 yupeigu 的回复:

shl字段加了isnull函数，改成这样：

    declare @sql varchar(8000)
    select @sql=isnull(@sql+'],[' , '')  + fdbs from daysphz  group by fdbs
    set @sql='['+@sql+']'
    exec ('select * from (select spid,isnull(shl,0),fdbs from daysphz ) a  pivot (sum(shl) for fdbs in ('+@sql+'))b 
           order by spid')

[ai_li7758521]

引用 3 楼 good212164 的回复:

有点报错

方法已经给出，报错应该是字符串@col拼接有问题，在exec前，select @col看下，自己调整下就可以了。我没有数据没法验证。

[LongRui888]

引用 9 楼 good212164 的回复:

这里面的列是动态的，不确定的呢， [quote=引用 6 楼 yupeigu 的回复:] shl字段加了isnull函数，改成这样：

    declare @sql varchar(8000)
    select @sql=isnull(@sql+'],[' , '')  + fdbs from daysphz  group by fdbs
    set @sql='['+@sql+']'
    exec ('select * from (select spid,isnull(shl,0),fdbs from daysphz ) a  pivot (sum(shl) for fdbs in ('+@sql+'))b 
           order by spid')

[/quote] 哦 ，明白，我刚才试了一下，因为你是用pivot来动态转置的，虽然很灵活，但是pivot中sum(shl)在group by时，必然会出现一些null值，但又不能改成isnull(sum(shl),0) 或者 sum(isnull(shl,0)),我试了 都不行。 我觉得实在不行，还是用普通的行转列技术，

[chen357313771]

参考如下代码：

CREATE TABLE #MyTable(
	ContainerName VARCHAR(12)
	, FRD_RateName NVARCHAR(12)
	, FRD_Charges INT
) 
INSERT INTO #MyTable
SELECT '45FR',N'标准报价',4000
UNION
SELECT '45FR',N'箱扣100',41
UNION
SELECT '45FR',N'退佣10 ',1
UNION
SELECT '45GP',N'标准报价',4100
UNION
SELECT '45GP',N'箱扣100 ',42
UNION
SELECT '45GP',N'退佣10',2
UNION
SELECT '40GP',N'标准报价',4200
UNION
SELECT '40GP', N'箱扣100', 43
UNION
SELECT '40GP',N'退佣10', 3
UNION
SELECT '20GP', N'标准报价',4300
UNION
SELECT '20GP',N'箱扣100', 44
UNION
SELECT '20GP',N'',4



DECLARE @Sql VARCHAR(MAX),@Sql1 VARCHAR(MAX)
SELECT @Sql=ISNULL(@Sql+',','')+'['+ContainerName+']' FROM #MyTable GROUP BY ContainerName

SELECT @Sql1=ISNULL(@Sql1+',','')+'ISNULL('+'['+ContainerName+']'+',0) AS '+'['+ContainerName+']' FROM #MyTable GROUP BY ContainerName

SELECT @Sql='SELECT FRD_RateName,'+@Sql1+'
             FROM #MyTable a 
             PIVOT(MAX(FRD_Charges) FOR ContainerName IN('+@Sql+')) p '      
EXEC(@Sql)

DROP TABLE #MyTable