SQL列数据转换为字符串

行列转换,将列数据转换为字符串输出

DECLARE @center_JZHW VARCHAR(500)
SET @center_JZHW = ( SELECT DISTINCT STUFF(
    ( SELECT  , + ce_code FROM ap_center WITH ( NOLOCK )
      WHERE   CE_PROVINCE = 浙江省 ORDER BY ce_code
      FOR XML PATH(‘‘)), 1, 1, ‘‘) AS jzhw
    FROM   ap_center t)
SELECT @center_JZHW
View Code

运行结果如下图,列CE_CODE用分隔符‘,‘输出

Function写法

DECLARE @ret VARCHAR(200)
        SET @ret = ‘‘
        SELECT  @ret = @ret + | + RTRIM(RT_GSX_COMPTIA_CODE)
        FROM    dbo.AP_REQUEST_RETURN_PART
        WHERE   rt_so_no = @so_no
        SET @ret = CASE WHEN LEN(@ret) > 0 THEN STUFF(@ret, 1, 1, ‘‘)
                        ELSE @ret
                   END
        RETURN @ret 
View Code


执行输出为

 

多列作为字段输出:

SELECT DISTINCT STUFF(
    ( SELECT  , + rt_loan_part FROM dbo.AP_REQUEST_RETURN_PART WHERE rt_so_no=IPTH14060001
      FOR XML PATH(‘‘)), 1, 1, ‘‘) AS part
    ,STUFF(
    ( SELECT  , + RT_GSX_COMPTIA_CODE FROM dbo.AP_REQUEST_RETURN_PART WHERE rt_so_no=IPTH14060001
      FOR XML PATH(‘‘)), 1, 1, ‘‘) AS COMPTIA_CODE 
    FROM   AP_REQUEST_RETURN_PART t
View Code

执行结果:

SQL列数据转换为字符串,古老的榕树,5-wow.com

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。