I am giving up the SQL solution, and now switching to Pandas. My goal is to merge the integer data as below:
Data input:
ACCT | SOURCES |
---|---|
A | 1 |
A | 2 |
B | 1 |
C | 4 |
expected output:
ACCT | SOURCES |
---|---|
A | 1,2 |
B | 1 |
C | 4 |
CodePudding user response:
Given:
ACCT SOURCES
0 A 1
1 A 2
2 B 1
3 C 4
Doing:
df.SOURCES = df.SOURCES.astype(str)
df = df.groupby('ACCT', as_index=False)['SOURCES'].agg(','.join)
print(df)
Output:
ACCT SOURCES
0 A 1,2
1 B 1
2 C 4
CodePudding user response:
You can use XMLAGG to concatenate them together. It puts spaces between the values, you can replace those with a comma. The innermost cast is if sources is actually defined as integer, not char/varchar.
select
acct,
oreplace(cast(xmlagg(cast(sources as varchar(5))) as varchar(10000)),' ',',')
from
<your table>
group by
acct
CodePudding user response:
I am not familiar with Teradata SQL environment.
Another solution I found is
select acct,
group_concat(distinct sources order by sources) as sources
from ...
group by acct
But it shows syntax error: expected something between '(' and the 'distinct' keyword.