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

细嗅蔷薇 2014-07-10 03:55:54



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终于能直接上传图片了。

...全文

420 14 打赏收藏转发到动态举报

写回复

用AI写文章

14 条回复

切换为时间正序

请发表友善的回复…

发表回复

细嗅蔷薇 2014-07-10

打赏
举报

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

發糞塗牆 2014-07-10

打赏
举报

nvarchar(500)的确很小

细嗅蔷薇 2014-07-10

打赏
举报

参数

长度给小了。

细嗅蔷薇 2014-07-10

打赏
举报


  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

字段断掉了。

细嗅蔷薇 2014-07-10

打赏
举报

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

發糞塗牆 2014-07-10

打赏
举报

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

细嗅蔷薇 2014-07-10

打赏
举报

引用 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);

發糞塗牆 2014-07-10

打赏
举报

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

细嗅蔷薇 2014-07-10

打赏
举报

引用 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

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

發糞塗牆 2014-07-10

打赏
举报

好像只能这样了

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

细嗅蔷薇 2014-07-10

打赏
举报

不行，没反应呃

發糞塗牆 2014-07-10

打赏
举报

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

细嗅蔷薇 2014-07-10

打赏
举报

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

發糞塗牆 2014-07-10

打赏
举报

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年我开始回帖开始就已经能上图了

有case when方式和2005之后的内置pivot和unpivot方法来实现，行列互转，可以分为静态互转，动态互转。

数据表： 列转行：利用max(case when then) max—聚合函数取最大值 (case course when ‘语文’ then score else 0 end) —判断 as 语文—别名作为列名 SELECT `name`, MAX( CASE WHEN course='\u8bed\u6587' THEN score END ) AS 语文, MAX( CASE WHEN course='\u6570\u5b66' THEN score END ) AS 数学, MAX( CASE WHEN course='\u82f1\u8bed' THEN score E

行列互转，是一个经常遇到的需求。实现的方法，有case when方式和2005之后的内置pivot和unpivot方法来实现。在读了技术内幕那一节后，虽说这些解决方案早就用过了，却没有系统性的认识和总结过。为了加深认识，再总结一次。行列互转，可以分为静态互转，即事先就知道要处理多少行（列）;动态互转,事先不知道处理多少行(列)。 --创建测试环境 USE tempdb; GO IF OBJECT_ID('dbo.Orders') IS NOT NULL DROP TABLE dbo.Orders; GO CREATE TABLE dbo.Orders ( orderid int

注意：列转行的方法可能是我独创的了，呵呵，因为在网上找不到哦，全部是我自己写的，用到了系统的SysColumns （一）行转列的方法先说说行转列的方法，这个就比较好想了，利用拼sql和case when解决即可实现目的 1：建立测试用的数据库代码如下:CREATE TABLE RowTest( [Name] [nvarchar](10) NULL,–名稱 [Course] [nvarchar](10) NULL,–課程名稱 [Record] [int] NULL–課程的分數) 2：加入测试用的数据库（先加入整齐的数据）代码如下:insert into RowTe

关于mysql 对行转列，列转行的sql 编写，只用case when的写法现有一组数据如下，该组数据网上随便找的，具体参考业务需求利用SQL达到如下效果实现语句 select name,max(case when subject=‘chinese’ then score end )chinese,max(case when subject=‘english’ then score ...