关于列转行时，加入case when判断。

[细嗅蔷薇]

select * 
from (select OST.TypeName as OSName ,bur.BureauName,osrr.[ReceiverNumber],osrr.[ReceiverDateTime]
   FROM [OA_OfficeSuppliestRequisitionedRecord] as OSRR
  left join OA_Employee as UserInfo on osrr.SuppliesUserId=UserInfo.EmpID
  left join [SYS_Bureau] as Bur on UserInfo.BureauID=Bur.BureauID
  left join OA_OfficeSuppliesType as OST on OSRR.SuppliesType=OST.ID)as b
 pivot( max(  b.[ReceiverNumber] ) for b.BureauName in (办公室,办信处)) m 

上面是行转列的sql语句。
下面是查询出来的结果，但是我想在sql语句中加入判断，ReceiverNumber为null就返回0 但是不知道该在哪里加。
求指导。关联的表好几张，就没有直接放上来。

吐槽一下，csdn终于能直接上传图片了。

[發糞塗牆]

select * from (select OST.TypeName as OSName ,bur.BureauName,osrr.[ReceiverNumber],osrr.[ReceiverDateTime] FROM [OA_OfficeSuppliestRequisitionedRecord] as OSRR left join OA_Employee as UserInfo on osrr.SuppliesUserId=UserInfo.EmpID left join [SYS_Bureau] as Bur on UserInfo.BureauID=Bur.BureauID left join OA_OfficeSuppliesType as OST on OSRR.SuppliesType=OST.ID)as b pivot( max( isnull(b.[ReceiverNumber],0) ) for b.BureauName in (办公室,办信处)) m 试试。从2012年我开始回帖开始就已经能上图了

[细嗅蔷薇]

消息 102，级别 15，状态 1，第 7 行 '(' 附近有语法错误。 以前是非要上传的自己相册 然后在用链接吧。

[發糞塗牆]

SELECT  *
FROM    ( SELECT    OST.TypeName AS OSName ,
                    bur.BureauName ,
                    ISNULL(osrr.[ReceiverNumber], 0) [ReceiverNumber] ,
                    osrr.[ReceiverDateTime]
          FROM      [OA_OfficeSuppliestRequisitionedRecord] AS OSRR
                    LEFT JOIN OA_Employee AS UserInfo ON osrr.SuppliesUserId = UserInfo.EmpID
                    LEFT JOIN [SYS_Bureau] AS Bur ON UserInfo.BureauID = Bur.BureauID
                    LEFT JOIN OA_OfficeSuppliesType AS OST ON OSRR.SuppliesType = OST.ID
        ) AS b PIVOT( MAX(b.[ReceiverNumber]) FOR b.BureauName IN ( 办公室, 办信处 ) ) m

[细嗅蔷薇]

不行，没反应呃

[發糞塗牆]

好像只能这样了

SELECT  OSNAME ,
        RECEIVERDATETIME ,
        ISNULL(办公室, 0) 办公室 ,
        ISNULL(办信处, 0) 办信处
FROM    ( SELECT    OST.TypeName AS OSName ,
                    bur.BureauName ,
                    osrr.[ReceiverNumber] ,
                    osrr.[ReceiverDateTime]
          FROM      [OA_OfficeSuppliestRequisitionedRecord] AS OSRR
                    LEFT JOIN OA_Employee AS UserInfo ON osrr.SuppliesUserId = UserInfo.EmpID
                    LEFT JOIN [SYS_Bureau] AS Bur ON UserInfo.BureauID = Bur.BureauID
                    LEFT JOIN OA_OfficeSuppliesType AS OST ON OSRR.SuppliesType = OST.ID
        ) AS b PIVOT( MAX(b.[ReceiverNumber]) FOR b.BureauName IN ( 办公室, 办信处 ) ) m

[细嗅蔷薇]

引用 5 楼 DBA_Huangzj 的回复:

好像只能这样了

SELECT  OSNAME ,
        RECEIVERDATETIME ,
        ISNULL(办公室, 0) 办公室 ,
        ISNULL(办信处, 0) 办信处
FROM    ( SELECT    OST.TypeName AS OSName ,
                    bur.BureauName ,
                    osrr.[ReceiverNumber] ,
                    osrr.[ReceiverDateTime]
          FROM      [OA_OfficeSuppliestRequisitionedRecord] AS OSRR
                    LEFT JOIN OA_Employee AS UserInfo ON osrr.SuppliesUserId = UserInfo.EmpID
                    LEFT JOIN [SYS_Bureau] AS Bur ON UserInfo.BureauID = Bur.BureauID
                    LEFT JOIN OA_OfficeSuppliesType AS OST ON OSRR.SuppliesType = OST.ID
        ) AS b PIVOT( MAX(b.[ReceiverNumber]) FOR b.BureauName IN ( 办公室, 办信处 ) ) m

好吧，其实想做个动态的，这样又变成静态的了。

[發糞塗牆]

你动态也是根据PIVOT( MAX(b.[ReceiverNumber]) FOR b.BureauName IN ( 办公室, 办信处 ) ) m 这两个列来的啊

[细嗅蔷薇]

引用 5 楼 DBA_Huangzj 的回复:

好像只能这样了

帮忙看下这个吧。一直提示 消息 105，级别 15，状态 1，第 8 行 字符串 'ReceiverDa' 后的引号不完整。 消息 102，级别 15，状态 1，第 8 行 'ReceiverDa' 附近有语法错误。 不知道哪转义错误了。

  create proc Proc_BureauReceiverStatistics
  @ReceiverDateTimeBegin nvarchar(500),
  @ReceiverDateTimeEnd nvarchar(500)
  as
  declare @BurName nvarchar(500)
  set @BurName=''; 
  select @BurName=@BurName+','+BureauName from [SYS_Bureau] where BureauID>1 group by BureauName
  set @BurName=STUFF(@BurName,1,1,'');
  
  if @ReceiverDateTimeBegin is not null
  begin
  set @BurName='
  select * from (select OST.TypeName as OSName ,bur.BureauName,osrr.[ReceiverNumber],osrr.[ReceiverDateTime]
   FROM [OA_OfficeSuppliestRequisitionedRecord] as OSRR
  left join OA_Employee as UserInfo on osrr.SuppliesUserId=UserInfo.EmpID
  left join [SYS_Bureau] as Bur on UserInfo.BureauID=Bur.BureauID
  left join OA_OfficeSuppliesType as OST on OSRR.SuppliesType=OST.ID)as b
  pivot( max( b.[ReceiverNumber]) for b.BureauName in ('+@BurName+'))m 
  where [ReceiverDateTime] between '''+@ReceiverDateTimeBegin+''' and '''+@ReceiverDateTimeEnd +''';';
  end else begin
   set @BurName='
  select * from (select OST.TypeName as OSName ,bur.BureauName,osrr.[ReceiverNumber],osrr.[ReceiverDateTime]
   FROM [OA_OfficeSuppliestRequisitionedRecord] as OSRR
  left join OA_Employee as UserInfo on osrr.SuppliesUserId=UserInfo.EmpID
  left join [SYS_Bureau] as Bur on UserInfo.BureauID=Bur.BureauID
  left join OA_OfficeSuppliesType as OST on OSRR.SuppliesType=OST.ID)as b
  pivot( max( b.[ReceiverNumber]) for b.BureauName in ('+@BurName+'))m  ';
  end
  exec(@BurName);

[發糞塗牆]

exec(@BurName)改成print(@BurName)看看里面的结果

[细嗅蔷薇]

动态的是从数据表里查出来的。不是写死的。

[细嗅蔷薇]

  select * from (select OST.TypeName as OSName ,bur.BureauName,osrr.[ReceiverNumber],osrr.[ReceiverDateTime]
   FROM [OA_OfficeSuppliestRequisitionedRecord] as OSRR
  left join OA_Employee as UserInfo on osrr.SuppliesUserId=UserInfo.EmpID
  left join [SYS_Bureau] as Bur on UserInfo.BureauID=Bur.BureauID
  left join OA_OfficeSuppliesType as OST on OSRR.SuppliesType=OST.ID)as b
  pivot( max( b.[ReceiverNumber]) for b.BureauName in (办公室,办信处,督查处,复查复核处,机关党委,接访处,网络处,综合处))m 
  where [ReceiverDat

字段断掉了。

[细嗅蔷薇]

参数 长度给小了。

[發糞塗牆]

nvarchar(500)的确很小

[细嗅蔷薇]

谢谢版主，感觉这版人好少啊。