| 网站首页 | 业界新闻 | 小组 | 交易 | 人才 | 下载频道 | 博客 | 代码贴 | 在线编程 | 编程论坛
共有 488 人关注过本帖
标题:sqlserver text类型的列怎么转行?
只看楼主 加入收藏
zzz452838868
Rank: 1
等 级:新手上路
帖 子:2
专家分:0
注 册:2019-9-30
结帖率:0
收藏
已结贴  问题点数:20 回复次数:2 
sqlserver text类型的列怎么转行?
sqlserver text类型的列怎么转行?
select fymc,BRZD,BRXB,HZNL,FYSL,YYTS,YLDW,SYPC,YFMC,HIS_YPBM,
(case when DMMC='药品用量审查' then cast(GZXX AS varchar(max)) else GZXX end )as '药品用量审查' ,
(case when DMMC='剂量限制' then cast(GZXX AS varchar(max)) else GZXX end )as '剂量限制' ,
(case when DMMC='成药草药相互作用' then GZXX else GZXX end )as '成药草药相互作用' ,
(case when DMMC='成药相互作用' then GZXX else GZXX end )as '成药相互作用' ,
(case when DMMC='抗生素重复用药' then GZXX else GZXX end )as '抗生素重复用药' ,
(case when DMMC='特殊人群之特殊年龄审查' then GZXX else GZXX end )as '特殊人群之特殊年龄审查' ,
(case when DMMC='性别限制' then GZXX else GZXX end )as '性别限制' ,
(case when DMMC='药物禁忌症审查' then GZXX else GZXX end )as '药物禁忌症审查' ,
(case when DMMC='注射药物配伍审查' then GZXX else GZXX end )as '注射药物配伍审查' ,
(case when DMMC='药物相互作用审查' then GZXX else GZXX end )as '药物相互作用审查'  from


 (
SELECT
        c.DMMC,
        CASE WHEN cast(A.GZXX_TSXX  AS varchar(4000))IS NULL OR LEN(CAST(A.GZXX_TSXX AS VARCHAR(4000))) <= 0 THEN A.GZXX ELSE CAST( A.GZXX_TSXX as varchar(4000))END GZXX,
        cast(A.FYMC as varchar(1000))as fymc ,
       dbo.f_strMzzd(A.YSJZXH,A.HGSJ) BRZD,
       A.BRXB,
       A.HZNL,
       A.FYSL,
       A.YYTS,
       A.DCYL,
       A.YLDW,
       A.SYPC,
       f.YFMC,
       A.HIS_YPBM  
FROM  BJYB_HGJL_MZ A,
      BJYB_YBGZ B LEFT JOIN BJYB_SJZD C ON C.DMSB = B.GZLBM AND C.DMLB = '100'
      ,dbo.BJYB_FYHG_MZ_CFXX d  ,dbo.BJYB_YPXX_YPYF  f ,dbo.BJYB_FYHG_MZ_MZZD e
WHERE A.GZBM = B.GZBM

and a.CFHM=d.CFHM and a.HIS_YPBM=d.HIS_YPBM  and d.YPYF=f.YPYF
and a.ysjzxh=e.ysjzxh
AND
      
      ( ( A.HGSJ >= '2019-9-27 0:0:0.000' And
          A.HGSJ <= '2019-9-29 23:59:59.000' And
          1 = 1
        ) Or
        ( A.JYRQ >= '2019-9-27 0:0:0.000' And
          A.JYRQ <= '2019-9-29 23:59:59.000' And
          1 = 2
        )
      ) And
      (A.GZBM LIKE '0' + '%' OR '0' = '0') And
      ((A.SHBZ = '0' And '0' = '0') oR
       (A.SHBZ > '0' And '0' = '1') OR
       '0' = '-1'
      ) And
      (A.KSBM = '-1' Or '-1' = '-1') AND
      (0 = 0 Or
        ( 0 = 1 And
          A.BRXZLX in (SELECT XZBM FROM V_YBFX_BRXZ)
        )
      )

 
    ) a
   

case wehn 中的GZXX这个字段就是text类型的,用case转的话,case转的行的值都是一样的。
2019-09-30 20:04
zzz452838868
Rank: 1
等 级:新手上路
帖 子:2
专家分:0
注 册:2019-9-30
收藏
得分:0 
已解决,参考如下:
select *
 from  (
SELECT
        c.DMMC,
         CAST( A.GZXX_TSXX as varchar(4000)) GZXX,
        j.HIS_YPMC as fymc ,
       dbo.f_strMzzd(A.YSJZXH,A.HGSJ) BRZD,
       A.BRXB,
       A.HZNL,
       A.FYSL,
       A.YYTS,
       A.DCYL,
       A.YLDW,
       A.SYPC,
       f.YFMC,
       A.HIS_YPBM  
FROM  BJYB_HGJL_MZ A,
      BJYB_YBGZ B LEFT JOIN BJYB_SJZD C ON C.DMSB = B.GZLBM AND C.DMLB = '100'
      ,dbo.BJYB_FYHG_MZ_CFXX d  ,dbo.BJYB_YPXX_YPYF  f ,dbo.BJYB_FYHG_MZ_MZZD e,BJYB_ZSK_011 j
WHERE A.GZBM = B.GZBM and a.HIS_YPBM=j.HIS_YPBM

and a.CFHM=d.CFHM and a.HIS_YPBM=d.HIS_YPBM  and d.YPYF=f.YPYF
and a.ysjzxh=e.ysjzxh and a.GZBM=b.GZBM
AND
      
      ( ( A.HGSJ >= '2019-9-27 0:0:0.000' And
          A.HGSJ <= '2019-9-29 23:59:59.000' And
          1 = 1
        ) Or
        ( A.JYRQ >= '2019-9-27 0:0:0.000' And
          A.JYRQ <= '2019-9-29 23:59:59.000' And
          1 = 2
        )
      ) And
      (A.GZBM LIKE '0' + '%' OR '0' = '0') And
      ((A.SHBZ = '0' And '0' = '0') oR
       (A.SHBZ > '0' And '0' = '1') OR
       '0' = '-1'
      ) And
      (A.KSBM = '-1' Or '-1' = '-1') AND
      (0 = 0 Or
        ( 0 = 1 And
          A.BRXZLX in (SELECT XZBM FROM V_YBFX_BRXZ)
        )
      )

 
    ) a
   
    pivot( MAx(gzxx) FOR dmmc IN (药品用量审查,剂量限制,成药草药相互作用,成药相互作用,抗生素重复用药,特殊人群之特殊年龄审查,
    性别限制,药物禁忌症审查,注射药物配伍审查,药物相互作用审查))a

   
2019-09-30 22:16
林月儿
Rank: 16Rank: 16Rank: 16Rank: 16
来 自:湖南
等 级:版主
威 望:138
帖 子:2203
专家分:10050
注 册:2015-3-19
收藏
得分:20 
可以考虑后台分组,如java1.8的lambda的groupby分组
或者其他类型语言,先从数据库读取再处理

剑栈风樯各苦辛,别时冰雪到时春
2019-10-02 21:28
快速回复:sqlserver text类型的列怎么转行?
数据加载中...
 
   



关于我们 | 广告合作 | 编程中国 | 清除Cookies | TOP | 手机版

编程中国 版权所有,并保留所有权利。
Powered by Discuz, Processed in 0.079095 second(s), 9 queries.
Copyright©2004-2020, BCCN.NET, All Rights Reserved