时间戳

首页 > 解决方案 > 通过连接来自不同列的逗号分隔值来创建字符串

sql - 通过连接来自不同列的逗号分隔值来创建字符串

问题描述

我有一个表，其中在所有列中从另一个 SQL 插入值。表结构如

CREATE TABLE temp_t
(
     catalog_item_code VARCHAR(1000),
     line_trans_type VARCHAR(1000),
     ref_trans_no VARCHAR(1000)
)

和价值观一样

INSERT INTO temp_t 
VALUES (' DR-1002-0001, DR-1010-0001, DR-20180926-05, RO-M-2059, HU-3154-2040,  JRCEKB-SS-1550-0001',' SALES, SALES, RETURN, RETURN, SALES, SPO',' 201681, 201681, 201666, 201660, 201681, 201648')

目前，当我们为上表运行 SELECT 时，我们会得到如下输出：

在此处输入图像描述

我想制作一个格式良好的字符串(line_trans_type +' - '+ref_trans_no +char(9)+ catalog_item_code)，每列中的每个逗号分隔值（如果包含值）就像

 SALES - 201681     DR-1002-0001
 SALES - 201681     DR-1010-0001
 RETURN - 201666    DR-20180926-05
 RETURN - 201660    RO-M-2059
 SALES - 201681     HU-3154-2040
 SPO - 201648       JRCEKB-SS-1550-0001

我尝试使用以下查询，但它总是在每一行重复所有 catalog_item_code。

SELECT
    REPLACE(line_trans_type , ',', '-' + 
        REPLACE(ref_trans_no, ',', char(9) + REPLACE(catalog_item_code, ',', char(13))))
FROM
    temp_t

提前致谢。

标签： sqlsql-servertsqlsql-server-2012

解决方案

你可以试试这个：

SELECT A.Data +' - '+B.Data+  char(9)+C.Data
FROM
(
    SELECT ROW_NUMBER() OVER(ORDER BY line_trans_type)ROWNO,  Data
    FROM
    (
        SELECT A.line_trans_type,  
        Split.a.value('.', 'VARCHAR(100)') AS Data  
        FROM  
        (
        SELECT line_trans_type,  
            CAST ('<M>' + REPLACE(line_trans_type, ',', '</M><M>') + '</M>' AS XML) AS Data  
        FROM  temp_t
        ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a)
    )A 
)A 
JOIN
(
    SELECT ROW_NUMBER() OVER(ORDER BY ref_trans_no)ROWNO,  Data
    FROM
    (
        SELECT A.ref_trans_no,  
        Split.a.value('.', 'VARCHAR(100)') AS Data  
        FROM  
        (
        SELECT ref_trans_no,  
            CAST ('<M>' + REPLACE(ref_trans_no, ',', '</M><M>') + '</M>' AS XML) AS Data  
        FROM  temp_t
        ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a)
    )B
)B ON A.ROWNO=B.ROWNO
JOIN
(
    SELECT ROW_NUMBER() OVER(ORDER BY catalog_item_code)ROWNO,  Data
    FROM
    (
        SELECT A.catalog_item_code,  
        Split.a.value('.', 'VARCHAR(100)') AS Data  
        FROM  
        (
        SELECT catalog_item_code,  
            CAST ('<M>' + REPLACE(catalog_item_code, ',', '</M><M>') + '</M>' AS XML) AS Data  
        FROM  temp_t
        ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a)
    )C
)C ON A.ROWNO=C.ROWNO

推荐阅读