|
|
Archives of the TeradataForum
Message Posted: Thu, 04 Aug 2005 @ 18:11:07 GMT
Subj: | | Re: How to Concatenate Column Values using SQL/Macros |
|
From: | | Anomy Anom |
<-- Anonymously Posted: Thursday, August 04, 2005 12:47 -->
I would try something like this:
Regards, Paul
Create volatile Table t1 As (
Select 'Lesand' Last , 'Danny' Frst , 1 Codes
) with data
On commit preserve rows ;
Insert Into t1
Select 'Lesand' , 'Danny' , 2 ;
Insert Into t1
Select 'Lesand' , 'Danny' , 3 ;
Insert Into t1
Select 'Benedi', 'Eric' , 7 ;
Insert Into t1
Select 'Benedi', 'Eric' , 14 ;
Create volatile Table t2 As (
Select t1 . last , frst , codes , csum( 1 , last || frst, codes ) Seq
Group By last , frst
) with data
On commit preserve rows
;
Select a . last , a . frst
,
Case When c . codes is Null
Then a . codes || ',' || b . codes
Else a . codes || ',' || b . codes || ',' || c . codes
End codes
From (
Select *
From t2
Where seq = 1 ) a
Left Join (
Select *
From t2
Where seq = 2) b
On a . last = b . last
And a . frst = b . frst
Left Join (
Select *
From t2
Where seq = 3 ) c
On a . last = c . last
And a . frst = c . frst ;
| |