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
运行结果如下图,列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
执行输出为
多列作为字段输出:
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
执行结果:
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。