Merging multiple columns into one in DQL

[AK]

Hello Community,

I need help with DQL, I have a set of columns with names column1.1.1.0, column1.1.1.1, column1.1.1.2, column1.1.1.3.
These columns do not always contain values, but one of them always will.
For example:-

column1.1.1.0	column1.1.1.1	column1.1.1.2	column1.1.1.3
	message3
		message6
message1
			message8
message2
	message4
	message5
		message7
			message9

My goal is to merge all of these source columns into a single new column. Could you please advise on the best method to accomplish this?

Regards,
AK

[Julius_Loman · Accepted solution]

If only one field has value for each record, you can use fieldsAdd with the coalesce function (takes the first non-null value):

data record(column1="abc"), record(column2="def"), record(column3="xyz")
| fieldsAdd coalesced=coalesce(column1, column2, column3)

If there are multiple values and you need concatenation, use the concat command instead:

data record(column1="abc"), record(column1="abc", column2="def"), record(column3="xyz")
| fieldsAdd concatenated=concat(column1, column2, column3)

[AK]

@Julius_Loman, thank you for the useful response. This works however, I'm observing two more challenges

1. Number of columns are inconsistent across records (it could be 3, 4 or 5 based on the timeframe we select)
2. Column names themselves are not standardized (for e.g.- column2.1.1.0, column2.1.1.1, column2.1.1.2, column2.1.1.3). In the name, the word "column" is common.

Can we write a DQL query that dynamically coalesces columns, regardless of their count or names?

Thank you in advance.

Regards,
AK

[Julius_Loman]

I don't think that's possible to do dynamically at the moment. You can use describe to get the names at least.