Concatenate alternate characters in SQL language

Question

I have to make some manipulation in SQL.

I need to combine ColA and ColB in ColC - alternate text separated by > - like this: a1-b1> a2-b2>a3-b3;.

Example

ColA            | ColB             | ColC
----------------+------------------+-----------------------------
abcd > e > efg  | ppppp > ppt > pp | abcd-ppppp > e-ppt > efg-pp 
hij > kl > iiii | aaa > bbb > hhh  | hij-aaa > kl-bbb > iiii-hhh
aa              | fff              | aa-fff
a > bbb         | pp > a           | a-pp > bbb-a

And then add ColD to beginning of each string path of ColC > final result ColE

dc1 > dc2 > dc3;...

ColC                        | ColD | ColE              
----------------------------+------+----------------------------------------      
abcd-ppppp > e-ppt > efg-pp | 2016 | 2016abcd-ppppp > 2016e-ppt > 2016efg-pp
hij-aaa > kl-bbb > iiii-hhh | 2012 | 2012hij-aaa > 2012kl-bbb > 2012iiii-hhh           
aa-fff                      | 2017 | 2017aa-fff                       
a-pp > bbb-a                | 2014 | 2014a-pp > 2014bbb-a

How can I do that in T-SQL?

**You really, really, really, really need to normalize your database.** Read [Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad), where you will see a lot of reasons why the answer to this question is **Absolutely yes!** — Zohar Peled, Apr 22 '18 at 10:35
How about some DDL and sample data to go with your expected results? https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ — MJH, Apr 22 '18 at 10:39
It looks like you already have an answer here: https://stackoverflow.com/questions/49910473/concatenate-alternate-characters-from-different-columns-in-r-programming — MJH, Apr 22 '18 at 10:41

score 0 · Accepted Answer · answered Apr 22 '18 at 11:34

Recursive CTE might be best approach for you

declare @t table (ColA varchar(100), ColB varchar(100), ColD int)
insert into @t values
    ('abcd > e > efg', 'ppppp > ppt > pp', 2016)
    , ('hij > kl > iiii', 'aaa > bbb > hhh', 2012)
    , ('aa', 'fff', 2017)
    , ('a > bbb', 'pp > a', 2014)

;with rcte as (
    select
        *, ColA2 = cast(ColA + ' >' as varchar(2000))
        , ColB2 = cast(ColB + ' >' as varchar(2000))
        , ca = charindex('>', ColA + ' >'), cb = charindex('>', ColB + ' >')
        , ColE = cast('' as varchar(4000))
    from 
        @t
    union all
    select
        ColA, ColB, ColD
        , cast(stuff(ColA2, 1, ca + 1, '') as varchar(2000))
        , cast(stuff(ColB2, 1, cb + 1, '') as varchar(2000))
        , charindex('>', stuff(ColA2, 1, ca + 1, '')), charindex('>', stuff(ColB2, 1, cb + 1, ''))
        , cast(ColE + ' > ' + cast(ColD as varchar(9)) + left(ColA2, ca - 2) + '-' + left(ColB2, cb - 2) as varchar(4000))
    from
        rcte
    where
        ca > 0
)

select 
    ColA, ColB, ColD, stuff(ColE, 1, 3, '')
from 
    rcte
where 
    ca = 0

Output

ColA             ColB               ColD    ColE
---------------------------------------------------------------------
a > bbb          pp > a             2014    2014a-pp > 2014bbb-a
aa               fff                2017    2017aa-fff
hij > kl > iiii  aaa > bbb > hhh    2012    2012hij-aaa > 2012kl-bbb > 2012iiii-hhh
abcd > e > efg   ppppp > ppt > pp   2016    2016abcd-ppppp > 2016e-ppt > 2016efg-pp

Concatenate alternate characters in SQL language

1 Answers1