Doe anyone know of a way to do this?

select cellid,

ValueCount((COLLECT DISTINCT Network)) as networks,

StringJoinTokens(SPLIT(COLLECT DISTINCT Network), ',') as networknames,

ValueCount((COLLECT DISTINCT [radio])) as radios from my_networks Group by cellid ;

I'm trying to get the unique networknames in a comma separated list in a single column. I get the ValueCounts just fine, but I want to list the actual values as well in a concatenated string. The ValueCount is showing me the number of unique network names, and I can list all the networks by not using the SPLIT Collection, but I only want the unique networks. Any help is much appreciated.

cscrum,

Can you provide a small sample of data in a Manifold project?

Well I got it somewhat working. I used this to get just one set of data:

select cellid, ValueCount((COLLECT DISTINCT Network)) as networks, StringJoinTokens(Network, ', ') as networknames from

(select cellid, SPLIT(COLLECT DISTINCT Network) from my_table Group by cellid) Group by cellid order by cellid;

I get another set like this:

select cellid, ValueCount((COLLECT DISTINCT radio)) as radios, StringJoinTokens(radio, ', ') as technology from

(select cellid, SPLIT(COLLECT DISTINCT radio) from my_table Group by cellid) Group by cellid order by cellid;

But I want them both at the same time in the same table so I tried this:

Select cellid,ValueCount((COLLECT DISTINCT Network)) as networks, StringJoinTokens(Network, ','), ValueCount((COLLECT DISTINCT radio)) as radios, StringJoinTokens(radio, ',') from (select cellid, SPLIT(COLLECT DISTINCT Network), SPLIT(COLLECT DISTINCT radio) from my_table Group by cellid)

Group by cellid order by cellid;

however this last result while showing the unique values listed the string values twice and I'm not sure why:

I'd like to combine some other sums and counts with them as well, but if I could get this all in one table like shown above with the correct number of strings listed, that would be a good start.

cscrum, would you be able to attach a sample of the data as a *.map or *.mxb file?

It may help if people can check queries with a sample of what you are working with.

Its difficult without a sample of the data but just looking at the last query perhaps you need to add 'radio' in the group by in the inner sql statement:

... SPLIT(COLLECT DISTINCT radio) from my_table Group by cellid)

to ... SPLIT(COLLECT DISTINCT radio) from my_table Group by cellid, radio)