title: Spark Scala - concatenate column in rows in a certain order
body: <p>Okay, I have a table with column definitions and corresponding ordinal positions.  I'm building a metadata-driven ETL framework using Spark (scala).  I have a table that contains the following info:  </p>
  
  <ul>
  <li>tablename</li>
  <li>columnname </li>
  <li>datatype</li>
  <li>ordinalposition</li>
  </ul>
  
  <p>I have to build a CREATE TABLE statement from that data.  Not a big deal, right?  I tried what appears to be the standard answer:</p>
  
  <pre><code>var metadatadef = spark.sql("SELECT tablename, columnname, datatype, ordinalposition FROM metadata")
      .withColumn("columndef", concat($"columnname", lit(" "), $"datatype"))
      .sort($"tablename", $"ordinalposition")
      .groupBy("tablename")
      .agg(concat_ws(", ", collect_list($"columndef")).as("columndefs"))
  </code></pre>
  
  <p>But the sort() call seems to be ignored here.  Or between collect_list() and concat_ws() it gets reshuffled.  Given source data like this:</p>
  
  <pre><code>+-----------+--------------+---------------+-----------------+
  | tablename | columnname   | datatype      | ordinalposition |
  + ----------+--------------+---------------+-----------------+
  | table1    | IntColumn    | int           | 0               |
  | table2    | StringColumn | string        | 2               |
  | table1    | StringColumn | string        | 2               |
  | table2    | IntColumn    | int           | 0               |
  | table1    | DecColumn    | decimal(15,2) | 1               |
  | table2    | DecColumn    | decimal(15,2) | 1               |
  +-----------+--------------+---------------+-----------------+
  </code></pre>
  
  <p>I am trying to get output like this:</p>
  
  <pre><code>+-----------+----------------------------------------------------------------+
  | tablename | columndefs                                                     |
  +-----------+----------------------------------------------------------------+
  | table1    | IntColumn int, DecColumn decimal(15,2), StringColumn string    |
  | table2    | IntColumn int, DecColumn decimal(15,2), StringColumn string    |
  +-----------+----------------------------------------------------------------+
  </code></pre>
  
  <p>Instead, I wind up with something like this:</p>
  
  <pre><code>+-----------+----------------------------------------------------------------+
  | tablename | columndefs                                                     |
  +-----------+----------------------------------------------------------------+
  | table1    | IntColumn int, StringColumn string, DecColumn decimal(15,2)    |
  | table2    | StringColumn string, IntColumn int, DecColumn decimal(15,2)    |
  +-----------+----------------------------------------------------------------+
  </code></pre>
  
  <p>Do I need to build a UDF to ensure I get proper order?  I need the output to end up in a dataframe for comparison purposes, not just building the CREATE TABLE statement.</p>