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)))]

3 comments:

  1. select fullName: `$((string firstName),' " " ,/: string lastName) from person

    ReplyDelete
    Replies
    1. Thanks! Still learning :)

      Delete
  2. Thx Fahd, this helped me a lot to build a book orders today:)

    ReplyDelete