title: SQL Query to Move Rows to Columns in a table, plus aliases
body: <p>I'm trying to get data from one database and put it in the format of a second database, where the schemas are different.  I'm trying to get data that is in multiple rows of one table into a single row, with some logic to change the field names.   I will have a unique ID (encounterID), which will be a key in the result table, and is a foreign key in the multiple rows.</p>
  
  <p>Here is my desired output schema, a temp table:</p>
  
  <pre><code>CREATE TABLE #tmpSurgery
  (       
  EncounterID INT NULL,
  RvAmplitude DECIMAL(7,2) NULL,
  RvImpedance DECIMAL(7,2) NULL,
  RvPulseWidth DECIMAL(7,2) NULL,
  RvVoltage DECIMAL(7,2) NULL,
  RvCurrent DECIMAL(7,2) NULL,
  LvAmplitude DECIMAL(7,2) NULL,
  LvImpedance DECIMAL(7,2) NULL,
  LvPulseWidth DECIMAL(7,2) NULL,
  LvVoltage DECIMAL(7,2) NULL,
  LvCurrent DECIMAL(7,2) NULL,
  RvLvAmplitude DECIMAL(7,2) NULL,
  RvLvImpedance DECIMAL(7,2) NULL,
  RvLvPulseWidth DECIMAL(7,2) NULL,
  RvLvVoltage DECIMAL(7,2) NULL,
  RvLvCurrent DECIMAL(7,2) NULL,
  AtrialAmplitude DECIMAL(7,2) NULL,
  AtrialImpedance DECIMAL(7,2) NULL,
  AtrialPulseWidth DECIMAL(7,2) NULL,
  AtrialVoltage DECIMAL(7,2) NULL,
  AtrialCurrent DECIMAL(7,2) NULL
  ) 
  </code></pre>
  
  <p>Notice that there are fields with prefixes Rv-, Lv-, RvLv- and Atrial- for Amplitude, PulseWidth, Impedance, Voltage, and Current in different columns.   My Source table #tmpLeads has the following columns:</p>
  
  <pre><code>LeadID
  EncounterID
  LeadValue
  Amplitude
  PulseWidth
  Impedance
  Voltage
  Current
  </code></pre>
  
  <p>Where LeadValue is in ('Rv', 'Lv', 'RvLv', 'Atrial') and EncounterID is the same, and LeadID is the unique key for each row in the field, and doesn't need to be in the result table.</p>
  
  <p>In my case, there will usually only be two rows in #tmpLeads for each EncounterID, but the LeadValues could be any two of the distinct LeadValues above (never two 'Lv's, two 'Rv's, or 'RvLv's, etc, but an 'Lv' and an 'Atrial', or an 'Atrial' and an 'RvLv'.)</p>
  
  <p>So, finally, my questions:</p>
  
  <p>How do I select the values out of #tmpLeads to update #tmpSurgery, so that I update the appropriate values based on LeadValue, so I have one row?</p>