|
|
|
Forum Member
 
Group: Forum Members
Last Login: 8/20/2008 6:42:37 AM
Posts: 48,
Visits: 20
|
|
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.
Regards BS
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 11/12/2008 9:19:53 PM
Posts: 469,
Visits: 463
|
|
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).
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 8/20/2008 6:42:37 AM
Posts: 48,
Visits: 20
|
|
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. 
Regards BS
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 11/12/2008 9:19:53 PM
Posts: 469,
Visits: 463
|
|
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.
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 8/20/2008 6:42:37 AM
Posts: 48,
Visits: 20
|
|
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
Regards BS
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 11/12/2008 9:19:53 PM
Posts: 469,
Visits: 463
|
|
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;
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 8/1/2007 1:31:00 AM
Posts: 2,
Visits: 1
|
|
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
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 11/12/2008 9:19:53 PM
Posts: 469,
Visits: 463
|
|
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 | | | |