Value of a cell in multiple rows transformed into same cell

[paulm]

HI,

I have a table with multiple rows of similar data as below

CustomerName

Customer1

Customer2

Customer3

I would like to have these all in the same cell like

CustomerName

Customer1, Customer2, Customer3

Is there any build in function to do this?  The only thing I could think of is using multiple previous which could get quite wordy.

CustomerName&’,’&previous(CustomerName)&’,’&previous(previous(CustomerName)) etc

Thanks
Paul

[tresesco]

If you could explain the real scenario in more details, that would help us find a pattern for your solution.

[nagaiank]

Try some script like the following:

LOAD * Inline [
Customer,CustNo
A,1
A,2
A,3
B,11
B,12
B,13
];
B:
LOAD *,SubField(AllCustNo,',',1) as CustNo1,SubField(AllCustNo,',',2) as CustNo2,SubField(AllCustNo,',',3) as CustNo3;
LOAD Customer,Concat(CustNo,',') as AllCustNo Resident A Group By Customer;
DROP Field AllCustNo;

[paulm]

Hi Tresesco,

Thanks for the reply - I would like the equivalent function to listagg in SQL if you are familiar with that?

Thanks

Paul

[tresesco]

The equivalent function is Concat() in qv.

[paulm]

Hi,

This is good at mapping to different columns but I would like the values of each row to be within the same cell.

The Listagg is the function I would have used in SQL, I was wondering if there is a build in equivalent?

Tresesco, I am unsure how you would use concat here?  Could you please show an example?

The only way I can do this is

CustomerName&’,’&previous(CustomerName)&’,’&previous(previous(CustomerName)) etc

but when completing for 20+ rows it gets very wordy and untidy.

Thanks

Paul

[#7]

I had a similar issue here, but I don't understand how to proceed.

My table has (after an outer join to avoid too much tables(don't know if this matters)) three rows, one row is the productID, the second is the labelID and the third is 'text'.

'text' is the labeltext and the content could be one up to four labels.

There are round about 300 rows. So can I create a similar command as listagg?

I have absolutly no clue of this function. Is there an example to analyze how it works?

[#8]

The Concat Function

That blog and the technical brief it links to might help