title: SQL query to create a single record view of multiple records - Dynamic Stored Procedure?
body: <p>Ok, I think there must be an easier way to do this, I would like to do it dynamically instead of hand writing every line. I am more familiar with MySQL than SQL Server 2008, but I'm not sure how I would do this in MySQL either.</p>
  
  <p>I have 3 tables. </p>
  
  <pre><code>Table 1:  USER       id | email | password
  Table 2:  METADATA   id | name   (list of fields I need to know about user)
  Table 3: USER_META   id | uid | name | value (where I store the user meta data)
  </code></pre>
  
  <p>I do not hard code the METADATA because it changes for each instance of this application. but in this example, lets just say the meta data is "eyes", "phone" , "city" (there are actually many more, there might be 20 this week and 40 next week)</p>
  
  <p>So the way it works is when a user registers, I build the registration form using the METADATA table to build the form. It creates a form with "eyes" , "phone" and "city".</p>
  
  <p>When I save the form, I save a single record into the <code>USER_META</code> table for each data item.
  So when registering, I get 3 inserts:</p>
  
  <pre><code>  insert into USER_META(uid,name,value) values (5,"eyes","brown")
    insert into USER_META(uid,name,value) values (5,"phone","555-1212")
    insert into USER_META(uid,name,value) values (5,"city","San Francisco")
  </code></pre>
  
  <p>Now, I want to write a Stored Procedure that will return a record like this"</p>
  
  <pre><code>EXECUTE Get_Meta 5
  </code></pre>
  
  <p>returns:</p>
  
  <pre><code>uid |  email  |  eyes | phone | city
  5    x@x.com    brown   555-1212  San Francisco;
  </code></pre>
  
  <p>I could write a long hard coded select statement like:</p>
  
  <pre><code>DECLARE @UID int;
  SELECT  id, email,  
  (select value from USER_META
   where uid = @UID and name = 'EYES') as EYES,
  (select value from USER_META
   where uid = @UID and name = 'PHONE') as PHONE,
  (select value from USER_META
   where uid = @UID and name = 'CITY') as CITY,
  FROM  USER 
  where id = @UID;
  </code></pre>
  
  <p>But that kind of defeats the whole purpose, and I have to write it again every week whenever the metadata requirements change (eg when we launch another instance).</p>
  
  <p>I would like to have something like this: (forgive me I am not very experienced with Advanced SQL, so maybe this is easy and I just don't know how to do it?) I will write it in basic code terms to try and explain my thinking:</p>
  
  <pre><code>DECLARE @UID int;
  DECLARE @META_NAMES array;
  @META_NAMES = (select NAME from METADATA);
  
  SELECT  id, email,  
   for each (@META_NAMES as $THIS_NAME) {
  
    (select value from USER_META
     where uid = @UID and name = '$THIS_NAME') as $THIS_NAME,
  
  }
  FROM  USER 
  where id = @UID;
  </code></pre>
  
  <p>Is there a way to write this in SQL Server 2008?</p>