Make String by concatenating comma separate values from different columns

Question

I have a table in which values inserted from another SQL in all columns. Table structure like as

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

And values like as

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')

Currently when we run SELECT for above table, we get output like:

I want to make a well formed string(line_trans_type +' - '+ref_trans_no +char(9)+ catalog_item_code) with each of the comma separated value from every column(if contain values) like

 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

I tried with the following query but it always repeats all catalog_item_code with every row.

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

Thanks in advance.

Upgrade your database to 2017 and use STRING_AGG.

Tim Biegeleisen
– Tim Biegeleisen

2021-08-30 03:57:03 +00:00
Commented Aug 30, 2021 at 3:57 — Tim Biegeleisen
– Tim Biegeleisen, Commented Aug 30, 2021 at 3:57

Stu · Accepted Answer · 2021-08-30 08:30:18Z

The following is one way you can do this in SQL 2012. Firstly create the following function to split a comma-delimited string into rows:

create function dbo.SplitString(@string varchar(1000))
returns table
as
return(  
  select Item = y.i.value('(./text())[1]', 'varchar(1000)'), Row_Number() over(order by (select 1/0)) rn
  from ( 
    select x = Convert(xml, '<i>' + Replace(@string, ',', '</i><i>') + '</i>').query('.')
  ) as a cross apply x.nodes('i') as y(i)
);

Then you can use this to return your 3 string values as 3 sets which can be joined:

with c as (
    select v.item, v.rn
    from temp_t t
    cross apply dbo.splitstring(t.catalog_item_code)v
), l as (
    select v.item, v.rn
    from temp_t t
    cross apply dbo.splitstring(t.line_trans_type)v
), r as (
    select v.item, v.rn
    from temp_t t
    cross apply dbo.splitstring(t.ref_trans_no)v
)
select Concat(l.item, ' - ', r.item, Char(9), c.item)
from c 
join l on c.rn=l.rn
join r on r.rn=c.rn

Example DB<>Fiddle

Atique Rahman · Accepted Answer · 2021-08-30 05:19:51Z

You can try this:

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

Collectives™ on Stack Overflow

Make String by concatenating comma separate values from different columns

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related