Analytics
Enthusiast

Concatenate Vertically?

Dear Who know this,
If I had 3 rows of data like this:

Number****CITY
1****USA
2****NYK
3****HKG

In excel, there are '&' function to concatenate vertically,
e.g =ROW1&","&ROW2&","&ROW3
The result= USA,NYK,HKG

How do I do this on SQL?

Many Thanks.
12 REPLIES
Enthusiast

Re: Concatenate Vertically?

Pure SQL solution would require a recursive query AFAIK.
Assuming the "NUMBER" column is contiguous without any gaps, and is unique, this is one possible solution.

WITH RECURSIVE CNTRINFO(NUMBER, COUNTRY)
AS
(
SELECT NUMBER, COUNTRY(VARCHAR(1000))
FROM mycntrytbl WHERE NUMBER = 1
UNION ALL
SELECT C2.NUMBER, C1.COUNTRY || ',' || C2.COUNTRY
FROM CNTRINFO C1, mycntrytbl C2
WHERE C2.NUMBER = C1.NUMBER + 1
)
SELECT COUNTRY FROM CNTRINFO
QUALIFY RANK() OVER(ORDER BY NUMBER DESC) = 1
;

Aggregate UDFs can be built to concatenate strings as well, but I don't think the ordering in which the strings are concatenated could be guaranteed. (unless the UDF stores it in a sorted storage - will make the UDF a bit complex).
Enthusiast

Re: Concatenate Vertically?

Dear Joe,
The result return:
USA
NYK
HKG

But I looking for something like
USA,NYK,HKG [All in one line].

However, thanks for your idea. :-)

Enthusiast

Re: Concatenate Vertically?

This is what I tried ...

CREATE TABLE CNTRYRECUR
(NUMBER INTEGER, COUNTRY VARCHAR(30));

INSERT INTO CNTRYRECUR VALUES(1, 'USA')

INSERT INTO CNTRYRECUR VALUES(2, 'NYK')

INSERT INTO CNTRYRECUR VALUES(3, 'HKG')

-- query

WITH RECURSIVE CNTRINFO(NUMBER, COUNTRY)
AS
(
SELECT NUMBER, COUNTRY(VARCHAR(1000))
FROM CNTRYRECUR WHERE NUMBER = 1
UNION ALL
SELECT C2.NUMBER, C1.COUNTRY || ',' || C2.COUNTRY
FROM CNTRINFO C1, CNTRYRECUR C2
WHERE C2.NUMBER = C1.NUMBER + 1
)
SELECT COUNTRY FROM CNTRINFO
QUALIFY RANK() OVER(ORDER BY NUMBER DESC) = 1
;

-- o/p

COUNTRY
USA,NYK,HKG

if you can describe you data more accurately, may be we can help. as I said, I had made the assumption that the number column is unique and contiguous.
Enthusiast

Re: Concatenate Vertically?

Thanks Joe,
Here is the actual data I am trying to do,
I had 3 customer_ID,every Cust_ID having different of country of visit, some 1 country, some more than 1.

The Format look like:
CustID***Country
3008***NYK
3008***HKG
4014***TKY
5813***SPG
5813***SWT
5813***CND

I will get the result of
NYK,HKG for 3008
TKY for 4014
SPG,SWT,CND for 5813

Enthusiast

Re: Concatenate Vertically?

Umm.... May be this is a job for someone smarter ;-)

I can only think of a volatile table based solution.given the limitations of recursion. (did I say crashed TD and now we have a bug to open ? :o )

Anyways here goes the VT based approach. There's a UDF based approach too, I can post that UDF if it's very much required to be in a single SQL ...

CREATE VOLATILE TABLE CUSTINFO_V(RNK INTEGER, CUSTID INTEGER, COUNTRY VARCHAR(30))
ON COMMIT PRESERVE ROWS;

INSERT INTO CUSTINFO_V
SELECT RANK() OVER(PARTITION BY CUSTID ORDER BY COUNTRY), CUSTID, COUNTRY
FROM CUSTINFO;

-- query

WITH RECURSIVE CUSTINFOR(LVL, CUSTID, COUNTRY) AS
(
SELECT 1 LVL, CUSTID, COUNTRY (VARCHAR(1000))
FROM CUSTINFO_V
WHERE RNK = 1

UNION ALL

SELECT C1.RNK, C1.CUSTID, C2.COUNTRY || ',' || C1.COUNTRY
FROM CUSTINFO_V C1, CUSTINFOR C2
WHERE C1.CUSTID = C2.CUSTID
AND C1.RNK = C2.LVL + 1
)
SELECT CUSTID, COUNTRY FROM CUSTINFOR
QUALIFY RANK() OVER(PARTITION BY CUSTID ORDER BY LVL DESC) = 1
;

-- o/p
CUSTID COUNTRY
3008 HKG,NYK
4014 TKY
5813 CND,SPG,SWT

-- cleanup
DROP TABLE CUSTINFO_V;
Fan

Re: Concatenate Vertically?

A question about the use of qualify rank and qualify row_number.
The following is applied after the from & where statement as you have used in the previous reply for a different question.

We use teradata sql assistant version 7.1.0.05

The help does not even recognise the "qualify rank" - any thoughts?
Bear in mind that the query runs without the qualify statement ......

qualify row_number ()
over (partition by
order by
A.Timestamp desc )
= 1
Enthusiast

Re: Concatenate Vertically?

These are OLAP functionalities of SQL, you would find info only in the Teradata manuals, specifically in functions and operators manual.

A simplified explanation on the two functions is as follows

ROW_NUMBER() will generate unique sequential values within a partition (or the entire result set, which is treated as a single partition if partitions are not applied) even if the two participating records in the same partition have the same values for the ordering columns. This is useful when at times you want to pick just one record of values and avoid dups using QUALIFY.

RANK() works again on partitions like ROW_NUMBER, just that if with in the partitions two records have same value on ordering columns, you will end up with same rank for both the records which would also mean that rank numbers won't be contiguous

Example

A B
1 1
1 5
2 1
2 1
2 10
2 14
3 6

SELECT A, B, ROW_NUMBER() OVER(PARTITION BY A ORDER BY B) RWNUM
FROM DATA001
ORDER BY 1, 2, 3
;

A B RWNUM
1 1 1
1 5 2
2 1 1
2 1 2
2 10 3
2 14 4
3 6 1

(note that records 3 & 4 , having values A=2,B=1 have diff row numbers though they have values on ordering columns and is in the same partition.

SELECT A, B, RANK() OVER(PARTITION BY A ORDER BY B) RNK
FROM DATA001
ORDER BY 1, 2, 3
;

A B RNK
1 1 1
1 5 2
2 1 1
2 1 1
2 10 3
2 14 4
3 6 1

note that for the same records mentioned above ie A=2,B=1 we have two records with same rank (=1)
And the next records (A=2,B=10) is rank 3, not rank 2.

So rank numbers within the same partition(in this case A=2) can have duplicate rank values, and rank values are not necessarily contiguous.
N/A

Re: Concatenate Vertically?

I have used the following technique to concatenate data across rows of data. You need to code for the max number of items that may appear in the list so this technique is not as robust as recursive SQL, but it does build the list in a single SQL statement:

select Custid
, trim(trailing ',' from c1||','||c2||','||c3||','||c4||','||c5) as C_All
from (select custid
, max(case
when Rnk = 1 then Country
else ''
end) as c1
, max(case
when Rnk = 2 then Country
else ''
end) as c2
, max(case
when Rnk = 3 then Country
else ''
end) as c3
, max(case
when Rnk = 4 then Country
else ''
end) as c4
, max(case
when Rnk = 5 then Country
else ''
end) as c5
from (select custid, country
, row_number()over(partition by custID order by country) as Rnk
from custinfo_v) as Inner1
group by 1) as Inner2
order by 1;
Enthusiast

Re: Concatenate Vertically?

Dear PRMcCluskey,
I had use you query to run on my machine, but I found a "issue" when running the above query.

Let say the custinfo_v table contain
(Cust_ID,Country)
(405,NY)
(405,USA)
(433,TKY)
(478,NY)
(478,HK)
(478,LA)

By running the query as :
=============================
select Custid
, trim(trailing ',' from c1||','||c2||','||c3) as C_All
from (select custid
, max(case when Rnk = 1 then Country else '' end) as c1
, max(case when Rnk = 2 then Country else '' end) as c2
, max(case when Rnk = 3 then Country else '' end) as c3
from (select custid, country
, row_number()over(partition by custID order by country) as Rnk
from custinfo_v) as Inner1
group by 1) as Inner2
order by 1;
=============================
The result only show:
405:
433:
478:NY,HK,LA

Please note that 405 and 433 showing no result.

Any suggestion?