title: SQL Syntax to Pivot multiple tables
body: <p>I have spent the past couple of days working on this and am going around in circles.</p>
  
  <p>My question is based around the answer I accepted in this post: <a href="https://stackoverflow.com/questions/1762305/sql-server-one-table-with-400-columns-or-40-tables-with-10-columns">stackoverflow question</a> </p>
  
  <p>I now have my data moved from a single 400 column table to a much more managable database structure with many thanks to Damir Sudarevic.</p>
  
  <p>My database looks like this:</p>
  
  <p><img src="https://i105.photobucket.com/albums/m221/paulbetteridge/jobfiles.jpg" alt="alt text"></p>
  
  <pre><code> CREATE TABLE JobFiles (
    JobID  UNIQUEIDENTIFIER PRIMARY KEY,
    MachineID UNIQUEIDENTIFIER REFERENCES Machines(MachineID),
    [Desc]  NVARCHAR(MAX),
    Name  NVARCHAR(255),
    JobOpen  BIT,
    [CreateDate]  DATETIME NOT NULL DEFAULT GETDATE(),
    [ModifyDate]  DATETIME NOT NULL DEFAULT GETDATE(),
    [CreatedByUser]  NVARCHAR(64)  DEFAULT '',
    [ModifiedByUser] NVARCHAR(64)  DEFAULT '')
  GO 
  
   CREATE TABLE JobParamType (
    ParamTypeID UNIQUEIDENTIFIER PRIMARY KEY,
    Name  NVARCHAR(255),
    [Desc]  NVARCHAR(MAX),
    IsTrait  NVARCHAR)
  
  GO
   CREATE TABLE JobParamGroup (
    ParamGroupID UNIQUEIDENTIFIER PRIMARY KEY,
    Name  NVARCHAR(255),
    [Desc]  NVARCHAR(MAX))
  
  GO
  
  
   CREATE TABLE JobParams (
    ParamID  UNIQUEIDENTIFIER PRIMARY KEY,
    ParamTypeID UNIQUEIDENTIFIER REFERENCES JobParamType(ParamTypeID),
    ParamGroupID UNIQUEIDENTIFIER REFERENCES JobParamGroup(ParamGroupID),
    JobFileID UNIQUEIDENTIFIER REFERENCES JobFiles(JobID),
    IsEnabled BIT)
  
  GO
  
   -- Text based property
   CREATE TABLE JobTrait (
    ParamID UNIQUEIDENTIFIER PRIMARY KEY REFERENCES JobParams(ParamID),
    Value  NVARCHAR(MAX) )
  GO 
  
   -- Numeric based property 
   CREATE TABLE JobMeasurement (
    ParamID UNIQUEIDENTIFIER PRIMARY KEY REFERENCES JobParams(ParamID),
    Value FLOAT,
    Format NVARCHAR(20),
    Unit NVARCHAR(MAX) )
  GO 
  </code></pre>
  
  <p>However, for a particular function of my application I need to list every JobParamType.Name row as columns containing a JobMeasurement.Value or JobTrait.Value as its data for each JobFiles.Name.</p>
  
  <p>JobParamType.IsTrait is used to determine if a value is a Measurement or Trait.</p>
  
  <p>i.e. </p>
  
  <pre><code>JobName  |  ParamName1      |  ParamName2      |  ParamName3       ... | ParamName400
  "MyJob"     MesurementValue    TraitValue         MesurementValue  ...   TraitValue  
  "TestJob"   MesurementValue    TraitValue         MesurementValue  ...   TraitValue  
  "Job2"      MesurementValue    TraitValue         MesurementValue  ...   TraitValue  
  
  etc
  </code></pre>
  
  <p>I have been playing with pivoting tables and have managed to get the columns from the JobParamType table by looking at examples and following them but it is now getting quite complicated because my data is split between several tables and it is starting to make my head hurt!!!</p>
  
  <pre><code>DECLARE @cols NVARCHAR(MAX)
  SELECT  @cols = STUFF(( SELECT DISTINCT TOP 10 PERCENT
                                  '],[' + tParams.Name
                          FROM    dbo.JobParamType  AS tParams
                          ORDER BY '],[' + tParams.Name
                          FOR XML PATH('')
                        ), 1, 2, '') + ']'
  print @cols
  </code></pre>
  
  <p>I am hoping someone could help me with the pivoting and getting the data from multiple tables.</p>
  
  <p>I hope this makes sense and I look forward to your help and discussions.</p>
  
  <p>Thank you in advanced.</p>