title: Split a single row into multiple rows with grouping data check - Hive
body: <p>Now I'm using the query below in hive to split a row into multiple rows, but I also want to group a "Product" column based on "Category" column each group will match by the order of the group and have ";" to sperate each group and have "," separate item in the group.</p>
  
  <pre><code>SELECT id, customer, prodcut_split  
  FROM orders lateral view explode(split(product,';')) products AS prodcut_split
  </code></pre>
  
  <hr>
  
  <p>Here is my data look like now</p>
  
  <pre><code>| id |  Customer| Category                  | Product                           |
  +----+----------+---------------------------+-----------------------------------+
  | 1  |  John    | Furniture; Technology     | Bookcases, Chairs; Phones, Laptop |
  | 2  |  Bob     | Office supplies; Furniture| Paper, Blinders; Tables           |
  | 3  |  Dylan   | Furniture                 | Tables, Chairs, Bookcases         |
  </code></pre>
  
  <p>my desired result will look like:</p>
  
  <pre><code>| id |  Customer| Category       | Product   |
  +----+----------+----------------+-----------+
  | 1  |  John    | Furniture      | Bookcases |
  | 1  |  John    | Furniture      | Chairs    |
  | 1  |  John    | Technology     | Phones    |
  | 1  |  John    | Technology     | Laptop    |
  | 2  |  Bob     | Office supplies| Paper     |
  | 2  |  Bob     | Office supplies| Blinders  |
  | 2  |  Bob     | Furniture      | Tables    |
  | 3  |  Dylan   | Furniture      | Tables    |
  | 3  |  Dylan   | Furniture      | Chairs    |
  | 3  |  Dylan   | Furniture      | Bookcases |
  </code></pre>