Concatenating texts while summarizing data

Database

Concatenating texts while summarizing data

Hi, I have a dataset of the following form:

mem ofr sales

1       Z      3

1       A      5

2       B      4

2       C      2

I want to summarize the data for each member which will have total sales and concatenated values of offers they have received.

The output should look like:

mem   ofr    sales

1        Z|A     8

2        B|C     6

4 REPLIES

Re: Concatenating texts while summarizing data

Hi experts, can anybody solve my above problem. Let me know if you need any further clarification.

Thanks,

Arijit

Enthusiast

Re: Concatenating texts while summarizing data

Which td version you are in?

select mem, tdstats.udfconcat(ofr),sum(sales) from your_table group by 1;

Re: Concatenating texts while summarizing data

Thanks Raja. I am using TD 13.11. It is giving error saying column tdstats not found in my_table.

Enthusiast

Re: Concatenating texts while summarizing data

Whew!!!  you have to get one else you can do something thus.

select id,max(case when rn=1 then ofr else '' end)||'|'||max(case when rn=2 then ofr else '' end),sale from 

(select mem,ofr,sum(sales) over(partition by id) sale, row_number() over(partition by id order by id) rn from test) t1 group by 1,3 order by 1