Concat Operator

Database
Enthusiast

Concat Operator

CREATE TABLE #t (a int NOT NULL, b VARCHAR(10), c VARCHAR(10))
GO
INSERT #t (a, b,c) VALUES (1, 'ab', 'cd')
INSERT #t (a, b,c) VALUES (1, 'ef', 'gh')
INSERT #t (a, b,c) VALUES (2, 'ij', 'kl')
INSERT #t (a, b,c) VALUES (2, 'mn', 'op')

Output:


a concatstringb concatstringc
1 'abef' 'cdgh'
2 'ijmn' 'klop'

My query is not working. Kindly help.

with tab_cte(a,b,c)
as
(select
a,b,c
from
tab)
select tab.a,
concat(tab_cte.b,tab.b),
concat(tab_cte.c,tab.c)
from tab inner join
tab_cte
on tab.a=tab_cte.a

5 REPLIES
Senior Apprentice

Re: Concat Operator

This is proprietary MS SQL Server syntax, Teradata only supports Standard SQL:

tab_cte.b || tab.b
Enthusiast

Re: Concat Operator

Whether it is SQL or teradata query. Iam not getting desired result .  I am getting 8 rows as result instead of desired 2 rows.

Teradata Employee

Re: Concat Operator

The definition of inner join in SQL says that every combination of rows in the two tables in the join that pass the where clause will generate a result row so each "1" row will join to both "1" rows - so the 4 rows will create 8 rows.

Will the data always be in pairs like the example data?
Enthusiast

Re: Concat Operator

Yes.Data should be in pair as mentioned . Is there is any way I get desired result....

Senior Apprentice

Re: Concat Operator

Depending on your actual data it might be as simple as adding a

WHERE tab_cte.b<tab.b