Saturday, April 16, 2016

kdb+/q - Concatenating columns

This post shows how you can concatenate values in two or more columns of a table in kdb, into a single value. Consider the following table:

q) person:([] firstName:`Alice`Bob`Charles;lastName:`Smith`Jones`Brown)
q) person
firstName lastName
------------------
Alice     Smith
Bob       Jones
Charles   Brown

In SQL, it's quite easy to concatenate columns, like this:

SQL> select firstName || ' ' || lastName as fullName from person;
fullName
--------
Alice Smith
Bob Jones
Charles Brown

In q, the same thing can be achieved by flipping the firstName and lastName columns and then calling sv to convert the resulting vector into a string, using a space separator. This is shown below:

q) select fullName:`$" "sv'string flip(firstName;lastName) from person

// in functional form:
q) colsToJoin:`firstName`lastName;
q) ?[person;();0b;enlist[`fullName]!enlist(`$sv';" ";(string;(flip;(enlist,colsToJoin))))]

// if there are many repeated names, you can use .Q.fu to improve performance:
q) select fullName:.Q.fu[{`$" "sv'string x};flip(firstName;lastName)] from person

// in functional form, with .Q.fu:
q) ?[person;();0b;enlist[`fullName]!enlist(.Q.fu;{`$" "sv'string x};(flip;(enlist,colsToJoin)))]

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.