All Forums Analytics
Benjamin Marcus 48 posts Joined 03/07
09 Jul 2007
Concatenate Vertically?

Dear Who know this,If I had 3 rows of data like this:Number****CITY1****USA2****NYK3****HKGIn excel, there are '&' function to concatenate vertically,e.g =ROW1&","&ROW2&","&ROW3The result= USA,NYK,HKGHow do I do this on SQL?Many Thanks.

Regards Benjamin Marcus
joedsilva 505 posts Joined 07/05
10 Jul 2007

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 CNTRINFOQUALIFY 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).

Benjamin Marcus 48 posts Joined 03/07
10 Jul 2007

Dear Joe,Thanks for your solution.The result return:USANYKHKGBut I looking for something likeUSA,NYK,HKG [All in one line].However, thanks for your idea. :-)

Regards
Benjamin Marcus

joedsilva 505 posts Joined 07/05
11 Jul 2007

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')-- queryWITH RECURSIVE CNTRINFO(NUMBER, COUNTRY)AS(SELECT NUMBER, COUNTRY(VARCHAR(1000))FROM CNTRYRECUR WHERE NUMBER = 1UNION ALLSELECT C2.NUMBER, C1.COUNTRY || ',' || C2.COUNTRYFROM CNTRINFO C1, CNTRYRECUR C2WHERE C2.NUMBER = C1.NUMBER + 1)SELECT COUNTRY FROM CNTRINFOQUALIFY RANK() OVER(ORDER BY NUMBER DESC) = 1;-- o/p COUNTRY USA,NYK,HKGif 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.

Benjamin Marcus 48 posts Joined 03/07
11 Jul 2007

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***Country3008***NYK3008***HKG4014***TKY5813***SPG5813***SWT5813***CNDI will get the result ofNYK,HKG for 3008TKY for 4014SPG,SWT,CND for 5813Many thanks in advance

Regards
Benjamin Marcus

joedsilva 505 posts Joined 07/05
11 Jul 2007

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_VSELECT RANK() OVER(PARTITION BY CUSTID ORDER BY COUNTRY), CUSTID, COUNTRYFROM CUSTINFO;-- queryWITH RECURSIVE CUSTINFOR(LVL, CUSTID, COUNTRY) AS(SELECT 1 LVL, CUSTID, COUNTRY (VARCHAR(1000))FROM CUSTINFO_VWHERE RNK = 1UNION ALLSELECT C1.RNK, C1.CUSTID, C2.COUNTRY || ',' || C1.COUNTRYFROM CUSTINFO_V C1, CUSTINFOR C2WHERE C1.CUSTID = C2.CUSTIDAND C1.RNK = C2.LVL + 1)SELECT CUSTID, COUNTRY FROM CUSTINFORQUALIFY RANK() OVER(PARTITION BY CUSTID ORDER BY LVL DESC) = 1;-- o/p CUSTID COUNTRY 3008 HKG,NYK 4014 TKY 5813 CND,SPG,SWT-- cleanupDROP TABLE CUSTINFO_V;

nicole_s 2 posts Joined 07/07
25 Jul 2007

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.05The 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 A.Email_Address order by A.Timestamp desc ) = 1

joedsilva 505 posts Joined 07/05
26 Jul 2007

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 followsROW_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 contiguousExampleA B1 11 52 12 12 102 143 6SELECT A, B, ROW_NUMBER() OVER(PARTITION BY A ORDER BY B) RWNUMFROM DATA001ORDER 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) RNKFROM DATA001ORDER 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 1note 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.

prmccluskey 1 post Joined 08/07
14 Aug 2007

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 Inner2order by 1;

Benjamin Marcus 48 posts Joined 03/07
22 Aug 2007

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_Allfrom (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 c3from (select custid, country, row_number()over(partition by custID order by country) as Rnkfrom custinfo_v) as Inner1group by 1) as Inner2order by 1;=============================The result only show:405:433:478:NY,HK,LAPlease note that 405 and 433 showing no result.Any suggestion?

Regards
Benjamin Marcus

BBR2 96 posts Joined 12/04
30 Aug 2007

BS,Try this SELECTMAX(CASE WHEN CNT =1 THEN val ELSE ' ' END ) || MAX(CASE WHEN CNT =2 THEN ',' || val ELSE ' ' END ) || MAX(CASE WHEN CNT =3 THEN ',' || val ELSE ' ' END ) || MAX(CASE WHEN CNT =4 THEN ',' || val ELSE ' ' END ) FROM (SELECT val,ROW_NUMBER() OVER(ORDER BY val) AS CNTFROM SHARED.TESTGROUP BY 1) t;Values considered sno val 1 USA 2 NYK 3 HKG 4 HKGResult HKG,NYK,USAThanks,Vinay Bagare

FRANKJUD 1 post Joined 04/11
18 Apr 2011

hi Gang,

I have a standard hierarchy table with the columns Parent Product Id, Child Product Id and level Number. All of the ulitimate parents do not have parents (Nulls). I need to show the entire lineage on one line (Parent Id, Child Id, Grand Child Id, Great Grandchild Id). The hierarchy only goes four levels deep. Each of the Ids reference a common product set. I've tried CASE, but I get '?'s in my Answer Set in Teradata. Any ideas?

mnylin 118 posts Joined 12/09
19 Apr 2011

For a fixed length hierarchy, it should be a simple matter of joining the table to itself n - 1 times where n is the number of levels.

You must sign in to leave a comment.