Databases
StackOverflow2013
Postsdbo
POSQL Syntax to Pivot multiple tables
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Syntax to Pivot multiple tables
    text
    copied!<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>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload