title: How do I split two columns in two separate tables into joined multiple rows in a view?
body: <p>I have two separate tables, each containing character-separated cells. One table contains all the key data, the other all the val data. In the application, both tables are loaded into recordsets, the two cells are split into two arrays and the arrays are then used 'side-by side' to create key/val pair.</p>
  
  <p>Trying to decouple the database from the application, I will basically create a view that emulates this behavior.</p>
  
  <p>I created some sample tables and data to better illustrate it.</p>
  
  <pre><code>/* Create tables */
  CREATE TABLE [dbo].[tblKeys](
      [KeyId] [int] NOT NULL, /*PK*/
      [KeyData] [nvarchar](max) NULL
  )
  CREATE TABLE [dbo].[tblValues](
      [ValId] [int] NOT NULL, /*PK*/
      [KeyId] [int] NOT NULL, /*FK*/
      [Language] [nvarchar](5) NOT NULL,
      [ValData] [nvarchar](max) NULL
  )
  
  /* Populate tables */
  INSERT INTO [dbo].[tblKeys] ([KeyId], [KeyData]) VALUES
      (1, '1|2|3'),
      (2, '1|2|3|4'),
      (3, '2|1')
  INSERT INTO [dbo].[tblValues] ([ValId], [KeyId], [Language], [ValData]) VALUES
      (1, 1, 'en',    'Apple|Orange|Pear'),
      (2, 1, 'sv-se', 'Äpple|Apelsin|Päron'),
      (3, 2, 'en',    'Milk|Butter|Cheese|Cream'),
      (4, 2, 'sv-se', 'Mjölk|Smör|Ost|Grädde'),
      (5, 3, 'en',    'Male|Female'),
      (6, 3, 'sv-se', 'Man|Kvinna')
  </code></pre>
  
  <p>The desired end result in the view looks like this:</p>
  
  <pre><code>| KeyId | KeyData | Language | ValData
  +-------+---------+----------+----------+
  |     1 |       1 |       en | Apple    |
  |     1 |       2 |       en | Orange   |
  |     1 |       3 |       en | Pear     |
  |     1 |       1 |    sv-se | Äpple    |
  |     1 |       2 |    sv-se | Apelsin  |
  |     1 |       3 |    sv-se | Päron    |
  ...
  </code></pre>
  
  <p>etc.</p>
  
  <p>I've seen similar questions here on StackOverflow, but they all deal with a single table being tilted in similar ways. I need to tilt both tables while using the positions of the data in the two columns KeyData and ValData as siginficants when recombining them into proper key/val pairs.</p>
  
  <p>How would I do this in an efficient manner?</p>
  
  <p>[Edit]: The database design is not mine. It's some old crap I inhereted. I <strong>know</strong> it's bad. Bad as in <em>horrendous</em>.</p>