Multiple values in single column

[jpjust]

Hi,

I have an table as below . Basically I tagged dataconnections in QMC.

Dataconnectionname          tag

ABC                                              Europe

ABC                                              US

 I want the table as below. (Tag values in one row)

Dataconnectionname          tag

ABC                                              Europe, US

Following is part of the script that output the above table.

Tags:

LOAD
[__KEY_root],
 concat([name_tag] , ',') as Tag.
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_tags]);

[DataConnections]:

LOAD
[__KEY_root],
[DataConnectionName]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__KEY_root]);

I tried this concat([name_tag] , ',')   in script but did not work with invalid expression.

Please let me know how to resolve this.

Thanks

[Taoufiq_Zarra]

@jpjust  with concat you must add group by

for example :

Input:

LOAD * INLINE [
Dataconnectionname,tag

ABC,Europe

ABC,US
];

output:
noconcatenate

load Dataconnectionname,concat(tag,',') as tag resident Input group by Dataconnectionname;

drop table Input;

the output:

[jpjust]

Thank you so much. 

My situation is like this. Please let me know how to accomplish.

[DataConnections]:
LOAD [id_u2] AS [DataConnectionID],
[name_u1] AS [DataConnectionName],
[__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull ([__KEY_root]);

[tags]:
LOAD
[id_u7],
[__FK_tags] AS [__KEY_root],
[name_tag] AS tag
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_tags]);

Now, in the above script, how can I do an group by and get the table output that I need.

The common field between both the table is  [__KEY_root]

Dataconnectionname          tag

ABC                                              Europe, US

[Taoufiq_Zarra · Accepted solution]

Maye be like :

DataConnections:
LOAD [id_u2] AS [DataConnectionID],
[name_u1] AS [DataConnectionName],
[__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull ([__KEY_root]);

left join

LOAD
[id_u7],
[__FK_tags] AS [__KEY_root],
[name_tag] AS tag
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_tags]);

output:
noconcatenate

load DataConnectionName,concat(tag,',') as tag resident DataConnections group by DataConnectionName;

drop table DataConnections;

[jpjust]

That worked. Really, Thanks much.