Turn several rows into one

[#1]

I have a straight table with sales info in columns like this:

SaleID Amount SalesPerson Region

A sale can have multiple Sale Persons on it. So I end up with 2 rows for 1 sale. Like this:

SaleID Amount SalesPerson Region

1 100 Tim USA

1 100 Sam USA

I would like to have it be on only one row:

SaleID Amount SalesPerson Region

1 100 Tim, Sam USA

Can I make an expression do this somehow? Thanks!

[erichshiino · Accepted solution]

When your table is already loaded (I will call it OriginalTable)

you can make:

load SaleIdD, Amount, concat(SalesPerson',', ') as SalesPeople,

//if you don't change at least one field name you will have to use noconcatenate before LOAD statement

Region

resident OriginalTable

group by SaleID, Amount, Region;

drop table OriginalTable;// if you don't drop it's better to change field names

[hector]

Hi

maybe you can use concat() and group by clauses like this

Load

SaleID,
Amount,
Region,
Concat(SalesPerson,',') as SalesPersonConcat
from/resident
group by
SaleID,
Amount,
Region

Rgds

PS. depending on your data, maybe you can use max(Amount) or Avg(Amount) or Sum(Amount), instead of grouping by the field

[#4]

I ended up just using the concat(SalesPerson',', ') in a expression and it works! hoooray!!