Analytics

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

07-09-2007
10:53 PM

07-09-2007
10:53 PM

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.

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

07-10-2007
12:47 AM

07-10-2007
12:47 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

07-10-2007
02:01 AM

07-10-2007
02:01 AM

Dear Joe,

Thanks for your solution.

The result return:

USA

NYK

HKG

But I looking for something like

USA,NYK,HKG [All in one line].

However, thanks for your idea. :-)

Thanks for your solution.

The result return:

USA

NYK

HKG

But I looking for something like

USA,NYK,HKG [All in one line].

However, thanks for your idea. :-)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

07-11-2007
01:16 AM

07-11-2007
01:16 AM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

07-11-2007
04:16 AM

07-11-2007
04:16 AM

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

Many thanks in advance

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

Many thanks in advance

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

07-11-2007
07:28 AM

07-11-2007
07:28 AM

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;

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

07-25-2007
08:22 PM

07-25-2007
08:22 PM

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

A.Email_Address

order by

A.Timestamp desc )

= 1

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

A.Email_Address

order by

A.Timestamp desc )

= 1

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

07-26-2007
12:30 AM

07-26-2007
12:30 AM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-14-2007
03:38 PM

08-14-2007
03:38 PM

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;

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-22-2007
04:33 AM

08-22-2007
04:33 AM

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?

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?