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写法

按 Ctrl+C 复制代码

按 Ctrl+C 复制代码

执行输出为

 

多列作为字段输出：

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

执行结果：